whats the best way to write this query

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

whats the best way to write this query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-27 : 00:12:35

Hi,

I'm not sure if best pratice dictates I should be using sub queries, or joins etc. I have to select data like such from a bunch of different tables. I would like the data returned in one row as such. I'm not exactly sure how the best way to write this is. Any help much appreciated.!

Result:
count1,count2,count3,count4,count5
1232, 43523, 2342, 32354, 23525

SELECT count(*) as count1 FROM tbl1 WHERE userID = userID
SELECT count(*) as count2 FROM tbl2 WHERE userID = userID
SELECT count(*) as count3 FROM tbl3 WHERE userID = userID
SELECT count(*) as count4 FROM tbl4 WHERE userID = userID
SELECT count(*) as count5 FROM tbl5 WHERE userID = userID

Thanks very much for clearing this up for me!

mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-27 : 01:07:06

DECLARE @c1 INT, @c2 INT, @c3 INT, @c4 INT, @c5 INT

SELECT @c1 = count(*) FROM tbl1 WHERE userID = @userID
SELECT @c2 = count(*) FROM tbl2 WHERE userID = @userID
SELECT @c3 = count(*) FROM tbl3 WHERE userID = @userID
SELECT @c4 = count(*) FROM tbl4 WHERE userID = @userID
SELECT @c5 = count(*) FROM tbl5 WHERE userID = @userID

select @c1 as count1, @c2 as count2, @c3 as count3, @c4 as count4, @c5 as count5

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-03-27 : 02:16:10

awesome, much appreciated

thanks again peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources