Record counting question from an SQL novice

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

SQL Server Development (2000)

Record counting question from an SQL novice

Author

Topic

CaptainSensible
Starting Member

4 Posts

Posted - 2007-03-14 : 12:40:10

Hi All

I need to count records from a joined pair of tables.
It needs to be a total count (i.e. a single numeric result) of entries from Table1 that exclude a given user that I supply as a parameter, and also exclude any entries where there are no corresponding records in the second joined table. The tables are joined on the ID fields.
I can see a way of getting the count using nested queries, but is there a neater way?

Table1

ID USER
-- ----
1 X
2 Y
3 Z
4 Y
5 Y
6 X

Table2

ID DATA
-- ----
1 text1
2 text2
1 text3
5 text4
4 text5
6 text6
2 text7

If supply the following user names in three seperate queries I would expect:-

X to give the result 3
Y to give the result 2
Z to give the result 5

Thanks in advance for any help.

James

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-14 : 13:22:07

Your sample data and results don't seem to tie up, exactly how do you get to
X to give the result 3
Y to give the result 2
Z to give the result 5
from the data you gave?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-14 : 13:37:08

Maybe I figured it out

SELECT count(distinct Table2.ID)
FROM Table1
INNER JOIN Table2 ON Table1.ID = Table2.ID
WHERE Table1.ID <> @UserID

CaptainSensible
Starting Member

4 Posts

Posted - 2007-03-14 : 13:40:31

OK

If I supply user X then I get ID numbers 2,3,4 & 5 from the first table. Of, those ID 3 has no corresponding records in the second table so gets ignored. That leaves a total count of 3.

Y gets 1,3 & 6 from the first table. Again 3 has no records in the second table which gives 2

Z gets 1,2,4,5 & 6 from the first table. All have records in the second table so nothing gets taken out giving the end result of 5.

Hope that explains it a bit better.

Cheers

James.

CaptainSensible
Starting Member

4 Posts

Posted - 2007-03-14 : 13:43:12

Oops, seem to have crossed over posts there.
I'll give it a try.

James.

CaptainSensible
Starting Member

4 Posts

Posted - 2007-03-16 : 06:23:11

Brilliant, it works.
Just had to change "Table1.ID" in the last line to "Table1.USER"
Also much faster than my original nested queries.

Thanks for the help snSQL.

James.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources