|
I have a select statement which joins various tables. But there are two tables which specify nearly same thing but values are different. Because of which when joining its giving more rows than expected. Now I want these two tables to be included in join.
like for example QCV_Values table it has Type column as "Oxygen", "NEB" and "Sleep". In another table (arusers) these values are as "HME" and "SLP" in the column "GroupName". "Oxygen" and "NEB" comes under samething "HME".
I need to add these tables in to join to avoid duplicacy in the rows. Can any body please help me in achiving this. Except that everything is working fine.
Can anybody please help me in that.
I am putting my query here
And I have to join the Type with the GroupName, but there is no specific table to relate these two.
SELECT InstanceKey, Category, v.QCDesc, oi.CustCenterName, au.MgrName, oi.InsName1, oi.InsName2,
oi.InsName3, oi.TherapyId, oi.PhysNum, oi.TherapyIdSlp, oi.PhysNumSlp, SalesPersonName
FROM dbo.QCV_Instances i
JOIN dbo.vw_OrderIntake oi ON i.InstanceKey=oi.CustNum
--JOIN dbo.Center c ON oi.CustCenterId=c.CenterID
JOIN dbo.arusers au ON oi.CustCenterId=au.CenterID
JOIN dbo.QCV_Values v ON v.QCID=i.QCValueId
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
You have a serious problem, it's called crappy data!
You either create a CASE in the join that maps each type to another or you create a mapping table that does the same thing. There are pros and cons for both solutions. CASE needs a change to a procedure if there is a new mapping, map table needs maintenance.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I concur. But how many times have we all see stuff like this?
I like your mapping table solution, unless this is a one off exercise in which case a case statement will run faster and won't need to be maintained.
|
|
|
|
|
Hi,
Yes, I have done it. As my office is far from my house I couldnt inform the same. But your reply is very usefull. Thanks a lot buddy.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
hi everybody,
In sql server 2005
I have a parent Table
Person(PersonID,PersonName)
PersonID personName
1 abc
2 xyz.
and child Table
BankAccount(AccountId,PersonId,Bank)
AccountId PersonId Bank
1 1 a
2 1 b
3 1 c
4 2 d
5 2 e
I Want to display result this way.
personId PersonName Bank
1 abc a,b,c
2 xyz d,e
Means display child tables rows result in parent table column.
modified on Wednesday, June 30, 2010 4:19 PM
|
|
|
|
|
You fail to mention what database you're using.
In Oracle you can use:
SELECT personid
,personname
,LISTAGG(bank, ',') WITHIN GROUP (ORDER BY bank) AS bank
FROM parent p,child c
WHERE p.personid = c.personid
GROUP BY personid,personname;
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
If you want to search for the answer, look at my last reply to the question below.
If you want the answer look at my second reply to indian143 (20:38 28 Jun '10) four questions down.
|
|
|
|
|
Hi All,
Can anybody please help me in executing a query using For Xml Path. Because query with this if I put it in my SQL Server editor, and run it, its giving errors. Is there any specific way to use For Xml Path. Or should I have anything to use it in my SQL Server 2008.
Because any query from any browser is giving errors. What should i do to get rid of it.
One more thing I found in my SQL Server is when I open the "new query" window in my SQL Server and try to write the "FOR XML" I get the RAW and Auto in the intellisense and I dont find the Path mode coming in the intellisense.
Am I miising any dll or any installation process. Because if I write any query using "For Xml Path" not at all compiling.
Can anybody help me in getting this done. I need to use the "For Xml Path", please help me out. Any links or any kind of help is very very welcoming.
Thanks in advance.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Tuesday, June 29, 2010 9:13 PM
|
|
|
|
|
Could you give us the error detail, transact sql statement as well.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Is your database in 2000 compatibility mode? FOR XML PATH was introduced in 2005.
|
|
|
|
|
I have run this exec to change the compatitbility
"EXEC sp_dbcmptlevel Ppsapp, 90". But it is not accepting 90 the "SQL Server 2005" compatibility level. It is giving following error
"Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Valid values of database compatibility level are 60, 65, 70, or 80.".
Please give me your idea for changing the compatibility level. May be thats why the query is not working.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
80 is Sql Server 2000, 70 is Sql Server 7 etc. You are connecting to a Sql Server 2000 instance not 2008. You cannot use FOR XML PATH. I suggest that you search google for "sql aggregate string concatenation". You may also find this link helps http://www.projectdmx.com/tsql/rowconcatenate.aspx[^]
|
|
|
|
|
Hi All,
We are trying to carry out a mail merge in Word 2007, which should be straight forward only we are running into issues.
We have a SQL Anywhere 10 ODBC Data Source setup on the machine and we are trying to access this database to populate certain areas of a letter. We used the step-by-step mail merge wizard and at Step 3 (Select Recipients), with "Use an Existing List" selected we click on the Browse hyperlink, which opened the "Select Data Source" window, where "+Connect to New Data Source" is selected, which in turn opens the "Data Connection Wizard". The ODBC Data Source previously created is selected and the username and password entered, the test on the connection is successful but when clicking the Ok button we receive the following message:
"The Data Connection Wizard cannot obtain a list of databases from the specified data source"
Does anyone know the steps needed to get around this issue?
Thank you,
Mel
|
|
|
|
|
how the datetime is written in a particular order using sql function?datetime format which i used is
Dec 2 2010 9:40:45:000PM
Dec 02, 2010 11:59:59:000PM
Dec 3 2010 3:45:54:000AM
Dec 3 2010 1:24:23:000AM
Dec 3 2010 12:15:27:000AM
how these datetimes in a correct order?pls help me...
|
|
|
|
|
So you have made the most basic error in data storage, you have stored your dates as varchar. You will have trouble until you reformat your data to datetime. Do it NOW!
However this may work on those formats
Select *
From Table1
Order by convert(datetime,StupidVarcharDateField)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Wow. You got my five for figuring out what the OP wanted. I read the post several times and was left scratching my head.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Chris Meech wrote: was left scratching my head
Been there many times, thankfully between the lot of us someone generally gets the idea the OP is trying to convey.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
convert(datetime,Field) that's it.
Thanks
Md. Marufuzzaman
I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.
|
|
|
|
|
Hi All,
I want a query to get all the column values as comma seprated values, but it should be group by in such a way that it should get the group by value in the prefix of the values of that group. Means the group column value should appear only once and other description column values for that group should precede it.
And all the rows should come as one string. Can anybody help me in writing this query.
Sorry I forgot to tell you that I am using SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Monday, June 28, 2010 5:16 PM
|
|
|
|
|
Can you show us a sample of the data and the output that you want. It would also be nice if you showed us how you have tried to tackle the task.
|
|
|
|
|
Like you have two columns Category and Item. Now I want all the items and categories should come in one single row but with different categories items should be separated by their categories.
For example
You have
Cat1 Item1
Cate2 Item 2
Cate3 Item3
Cate1 Item 2
Cate1 Item3
Cate2 Item1
Cate2 Item 3
Cate3 Item1
Cate3 Item2
Then my output should look as follows
cate1: Item1, Item2, ITem3/Cate2: Item1, Item2, Item3/Cate3:Item1, Item2, Item3.
Can you please help me in achieving it, in performance oriented way. They need that too.
Sorry I forgot to tell you that I am using SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
CREATE TABLE #data
(
Category VARCHAR(10),
Item VARCHAR(10)
);
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item 2');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item3');
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item 2');
INSERT INTO #data (Category, Item) VALUES ('Cate1', 'Item3');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate2', 'Item 3');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item1');
INSERT INTO #data (Category, Item) VALUES ('Cate3', 'Item2');
SELECT Category,
STUFF((SELECT ',' + Item AS [text()]
FROM #data AS d2
WHERE d2.Category = d1.Category
ORDER BY Item ASC
FOR XML PATH('')), 1, 1, '') as ItemList
FROM #data AS d1
GROUP BY Category;
DROP TABLE #data;
|
|
|
|
|
This query is not in SQL Server 2008. Should I change anything for running this in SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
I wrote this in Sql Server 2008.
|
|
|
|
|
Sorry for troubling you but it is giving error "Incorrect syntax near the keyword 'FOR'". And I dont know much about using FOR in T-SQL. If possible can you pls. help me in that. Why is it coming. I tried by moving ")", but doesnt work.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|