help with query - 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)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-04-11 : 19:03:36

Hi,

I'm having problems with what I think is a pretty simple query.

I have 2 tables

userDetails (userID,userName)
comments (userID,commentFromID,comment)

I want to select the COUNT of all the records in "userDetails" that has more than 0 rows in the comments table. (joined on USERID)

can anyone direct me on this one?

thanks very much once again!!
mike123

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-11 : 19:29:59

I think a simple join does the trick:

SELECT COUNT(*)
FROM userDetails u
INNER JOIN comments c
ON u.userID = c.userID

But if not, then:


SELECT COUNT(*)
FROM userDetails u
INNER JOIN
(
	SELECT userID, COUNT(*)
	FROM comments
	GROUP BY userID
	HAVING COUNT(*) > 0
) c
ON u.userID = c.userID

Tara Kizer
aka tduggan

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-04-11 : 22:59:32

couldnt get exactly what I needed with the first, but the second worked perfectly

thanks tara! :)

mike123

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-12 : 05:09:17

Tara - isn't this overcomplicating things?

If userId is unique in userDetails, I think you can simply use this:

select count(distinct userId) from comments

If it's not unique, I think you can use this:

select count(*) from userDetails where userId in (select userId from comments)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-12 : 12:02:16

Perhaps it is. But that's what I came up with based upon no data being given.

Tara Kizer
aka tduggan

Kristen
Test

22859 Posts

Posted - 2006-04-12 : 12:06:29

Tara,

Is your Second One the same as:


SELECT COUNT(DISTINCT u.userID)
FROM userDetails u
INNER JOIN comments c
ON u.userID = c.userID

If so I have no idea which is more efficient!

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-12 : 13:52:38

Perhaps, which is what my intention was for the first solution I provided. I just missed the DISTINCT part.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources