Jump to content


Photo

two table query - just point me in the right direction


  • Please log in to reply
2 replies to this topic

#1 drifter

drifter
  • Members
  • PipPipPip
  • Advanced Member
  • 189 posts
  • LocationWashington

Posted 22 October 2006 - 06:20 AM

OK - I have a table with users...

userid - name - etc - etc
1 - scott
2 - angela
3 - perte
4 - jamie

I have a secong table with hobbies...

id - userid - hobby
1 - 1 - golf
2 - 1 - swimming
3 - 2 - golf
4 - 4 - tennis
5 - 1 - bowling

I want to find all users that have a hobby of golf and swimming.

How do I do this?

Really I just need to get pointed in the right direction.

Thanks in advance
Scott

#2 extrovertive

extrovertive
  • Members
  • PipPipPip
  • Advanced Member
  • 235 posts

Posted 22 October 2006 - 08:28 AM

SELECT u.name
FROM users u
LEFT JOIN hobbies h
ON u.userid = h.userid
WHERE h.hobby = 'golf' OR h.hobby = 'swimming'
GROUP BY u.name
HAVING COUNT(*) = 2


#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 22 October 2006 - 08:37 PM

Or:

SELECT u.name, COUNT(h.userid) AS cnt
FROM users u
LEFT JOIN hobbies h
ON u.userid = h.userid
WHERE h.hobby = 'golf' OR h.hobby = 'swimming'
GROUP BY u.name
HAVING cnt = 2

Note that if you use LEFT JOIN, then technically you should be counting the non-null records in the "right" table, but it's irrelevant in this particular case since you're testing an equality.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users