Join a count? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Join a count?

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-04 : 00:52:51

I have a statement as follows:

SELECT TOP 10 nameOnline, userID, points FROM tblUserDetails WHERE GenderID = 2

I want to this modify this so that I can get the count of records in a column in another table and also whether a value exists in another table. Down below I have a tried to illustrate the tables, hopefully its readable :) . Basically I need this query to bring back the count of thumbs from tblTHumb, and whether or not a value exists in tblPic.

Thanks alot,
Mike

tblthumb
----------------------
userID thumbID

500 1
500 2
500 3

tblPic
----------------------

userID path

500 "images/blah.jpg"

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-07-04 : 01:17:46

Something like this would work:

SELECT TOP 10 nameOnline, userID, points
, (select count(*) as tblthumb_count from tblthumb) as tblthumb_count
, case
when exists(select * from tblPic) then "Y"
else "N"
end as PicExist
FROM tblUserDetails WHERE GenderID = 2

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-04 : 20:36:12

Thanks but I get the following and cant seem to figure out the error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Y'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'N'.

Cheers,
Mike

rksingh024
Yak Posting Veteran

56 Posts

Posted - 2002-07-05 : 00:43:31

Hi Mike,

Use single quotes instead of double quotes otherwise use
SET quoted_identifier OFF
----

See more details in BOL for quoted_identifier

Ramesh

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-05 : 02:18:50

thanks I can't believe I didnt notice that ... long day !!

ok the results being brought back arent exactly what I wanted, im not sure if its possible to bring back what I want but I'll re-explain better this time

I want to add on 2 columns to the recordset from a different table via a JOIN . Each row that is added on must correspond to the USERID of the row. Lets say for example the USERID in the first SELECT statement is 500, the two joined columns must be the count of userID in the other table. and the other column must be whether the value exists or not in the other column. and the next row would be userID 501 with and independant result for the 2 joined columns.

I hope this makes sense... Please let me know if I can clear it up anyway.

Cheers,
Mike

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-05 : 03:54:59

what about something like the following....you're getting into the realms of CORRELATED subqueries....there are other (more proper??) examples of this on this site....

SELECT TOP 10 nameOnline, userID, points, (select count(*) as tblthumb_count from tblthumb where tblUserDetails.userid = tblthumb.fkuserid) as tblthumb_count,
case when exists (select * from tblPic where tblUserDetails.userid = tblPic.fkuserid) then 'Y' else 'N' end as PicExist
FROM tblUserDetails
WHERE GenderID = 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources