Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
kirank
Yak Posting Veteran
58 Posts |
Posted - 2013-12-03 : 05:55:20
|
Here is my query which returns multiple rows SELECT R.name, R.age,R.DOB, ISNULL(D.Doc1,'NA') AS doc1, ISNULL(C.Doc2,'NA') AS doc2 FROM REQ R inner join RES S ON R.Request_Id=S.Request_Id inner join RES1 D ON D.Response_Id=S.Response_Id inner join REQ1 C ON C.Request_Id=R.Request_Id select * from RES1 where Response_Id = 111 -- return 3 select * from REQ1 where Request_Id = 222 --- returns 2 so at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows could you plz help me---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-03 : 06:25:32
|
[code]SELECT R.name, R.age,R.DOB, ISNULL(STUFF((SELECT ',' + Doc1 FROM RES1 D WHERE D.Response_Id=S.Response_Id FOR XML PATH('')),1,1,''),'NA') AS doc1,ISNULL(STUFF((SELECT ',' + Doc2 FROM REQ1 C WHERE C.Request_Id=R.Request_Id FOR XML PATH('')),1,1,''),'NA') AS doc2FROM REQ R inner join RES S ON R.Request_Id=S.Request_Id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
kirank
Yak Posting Veteran
58 Posts |
Posted - 2013-12-03 : 06:45:00
|
Thanks for the response.correct output get populate, but in desing view i need to perform some other opearation in it. comma separated values will not work for me.It would be better if i can get 3 records ( highest no from both the table RES1 or REQ1 )Thanks again---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-03 : 07:29:52
|
[code]SELECT R.name, R.age,R.DOB, ISNULL((SELECT MAX(Doc1) FROM RES1 D WHERE D.Response_Id=S.Response_Id),'NA') AS doc1,ISNULL((SELECT MAX(Doc2) FROM REQ1 C WHERE C.Request_Id=R.Request_Id),'NA') AS doc2FROM REQ R inner join RES S ON R.Request_Id=S.Request_Id[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
kirank
Yak Posting Veteran
58 Posts |
Posted - 2013-12-03 : 08:14:52
|
Hi, this is not returning all the records from RES1 & REQ1 table . it only return one single recordi want here 5 rows (RES1 -- 3 records, REQ1 - 2 records)---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-03 : 09:23:54
|
quote: Originally posted by kirank Hi, this is not returning all the records from RES1 & REQ1 table . it only return one single recordi want here 5 rows (RES1 -- 3 records, REQ1 - 2 records)---------------------------http://codingstuffsbykiran.blogspot.com | http://webdevlopementhelp.blogspot.com
You only asked for Max record rite?It would be better if i can get 3 records ( highest no from both the table RES1 or REQ1 )I cant understand what your actual expected output is. Unless you bring values in delimited you cant show them all and keep record to 1 per RequestId,responseId cpmbination.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|