drifter Posted October 22, 2006 Share Posted October 22, 2006 OK - I have a table with users...userid - name - etc - etc1 - scott2 - angela3 - perte4 - jamieI have a secong table with hobbies...id - userid - hobby1 - 1 - golf2 - 1 - swimming3 - 2 - golf4 - 4 - tennis5 - 1 - bowlingI 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 advanceScott Quote Link to comment Share on other sites More sharing options...
extrovertive Posted October 22, 2006 Share Posted October 22, 2006 [code]SELECT u.nameFROM users uLEFT JOIN hobbies hON u.userid = h.useridWHERE h.hobby = 'golf' OR h.hobby = 'swimming'GROUP BY u.nameHAVING COUNT(*) = 2[/code] Quote Link to comment Share on other sites More sharing options...
fenway Posted October 22, 2006 Share Posted October 22, 2006 Or:[code]SELECT u.name, COUNT(h.userid) AS cntFROM users uLEFT JOIN hobbies hON u.userid = h.useridWHERE h.hobby = 'golf' OR h.hobby = 'swimming'GROUP BY u.nameHAVING cnt = 2[/code]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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.