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 |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2008-09-17 : 13:03:26
|
| Hi,Just wondering the best strategy here.I have about 10 queries like this, and I'm wondering whats the best way to combine them ? They are all queries selecting single column count values, each from individual tabales. I need the query to bring back just 1 row, with all the values in the appropriately named columns. I believe I've been down this road before, but can't remember how to do it. I just looked up PIVOT and I don't think its the right tool for the job. Any tips much appreciated.Thanks,Mike123select count(*) as unread_Mail_Count from tblmessage where checked = 0 and deletedByRecipient = 0 AND messageTo = @userID AND date > @lastLoggedInselect count(*) as unread_IM_Count from tblInstantMessage where checked = 0 and deletedByRecipient = 0 AND messageToID = @userID AND date > @lastLoggedInselect count(*) as unread_Comment_Count from tblComment where active = 0 and deletedByRecipient = 0 AND commentTo = @userID AND date > @lastLoggedIn |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-09-17 : 13:10:37
|
| [code]select(select count(*) as unread_Mail_Count from tblmessage where checked = 0 and deletedByRecipient = 0 AND messageTo = @userID AND date > @lastLoggedIn) as col1,(select count(*) as unread_IM_Count from tblInstantMessage where checked = 0 and deletedByRecipient = 0 AND messageToID = @userID AND date > @lastLoggedIn) as col2,(select count(*) as unread_Comment_Count from tblComment where active = 0 and deletedByRecipient = 0 AND commentTo = @userID AND date > @lastLoggedIn) as col3...[/code] |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2008-09-17 : 18:34:12
|
| Hey Visakh16,Exactly what I was looking for... thats great , thank you!mike123 |
|
|
|
|
|
|
combine multiple single column queries to 1 result