Subquery in Select? - 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

SQL Server Development (2000)

Subquery in Select?

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-01-05 : 15:10:18

Best way to do this? Anyone? I've scoured the forums and can't find anything similar.

select MembersID,
(select COUNT(MemberPhotosID) as PhotoCount from MemberPhotos
where MemberPhotos.MembersID = Member.MembersID AND Private = 0 AND ApprovalCode = 1)
from Members
where @ParmMinPhotos <=
(select COUNT(MemberPhotosID) as PhotoCount from MemberPhotos
where MemberPhotos.MembersID = Member.MembersID AND Private = 0 AND ApprovalCode = 1)

Thanks in advance.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-05 : 15:38:55

Does this accomplish what you want?

select m.MembersID, COUNT(mp.MemberPhotosID) as PhotoCount
where MemberPhotos.MembersID = Member.MembersID AND Private = 0 AND ApprovalCode = 1)
from Members m
join MemberPhotos mp
on m.MembersID = mp.MembersID
where mp.Private = 0 AND mp.ApprovalCode = 1
group by m.MembersID
having @ParmMinPhotos <= COUNT(mp.MemberPhotosID)

I haven't tested it since there were no DDL statements to define the tables and no Insert statements to populate the tables.

If this is what you wanted, it will probably perform better than what you have written. The only way to know for sure is to test both in a neutral environment.

HTH

=================================================================

Where it is a duty to worship the sun, it is pretty sure to be a crime to examine the laws of heat. -John Morley, statesman and writer (1838-1923)

derketo
Starting Member

28 Posts

Posted - 2005-01-05 : 15:50:57

Thanks, you set me on the right track. This is what I came up with.

select members.MembersID,
(COUNT(MemberPhotosID)) as PhotoCount
from Members
left join memberphotos on members.membersid = memberphotos.membersid
group by members.membersid
having COUNT(MemberPhotosID) > 1

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-05 : 16:03:09

I'm not sure that you need to perform a LEFT JOIN instead of an OUTER JOIN. If there are no matching photos, your count is going to fail the HAVING clause anyway.

HTH

=================================================================

Where it is a duty to worship the sun, it is pretty sure to be a crime to examine the laws of heat. -John Morley, statesman and writer (1838-1923)

derketo
Starting Member

28 Posts

Posted - 2005-01-05 : 16:29:12

Thanks, bustaz. You're the man.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources