Does this need a curser - 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 2005 Forums

Transact-SQL (2005)

Does this need a curser

Author

Topic

sinky
Starting Member

1 Post

Posted - 2008-08-04 : 12:30:18

Hi,

I have three tables:

user
userpet
pet
petcategory

how can i tell if a user has a whole category of pets.

I can use:
Select * form pet where petid in (select * from userpet where userid = 8)

now i have all the records i think i needs with example results below:

petid:1
petdesc:cat
petcat:1
petid:2
petdesc:dog
petcat:1

now say category 1 only had two pets - how can i show this user has all the pets in the category.

Any help would be much appreciated.

Sinky

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 12:57:17

no need of cursor. just use like this

SELECT u.userID
FROM user u
JOIN userpet up
ON up.UserID=u.UserID
JOIN pet p
ON p.petID=up.petID
JOIN petcategory pc
ON pc.categoryID=p.categoryID
JOIN (SELECT categoryID,COUNT(DISTINCT petID) AS petcount
      FROM petcategory
      GROUP BY categoryID)t
ON t.categoryID=pc.categoryID
GROUP BY userID
HAVING COUNT(DISTINCT up.PetID) =MAX(petcount)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources