Page 1 of 1

Help with selecting multiple rows from LEFT JOINs

Posted: Mon Jun 02, 2008 8:09 pm
by Mr Tech
I have a table set-up called clubs. It is laid out like this:

Code: Select all

member_id | club_id
1         | 1
1         | 3
2         | 1
2         | 2
I have a query to search both the members table and the clubs table. I am wanting it to extract ONLY the members that have a club_id value set to 1 and 3 in the clubs table. It can't be one or the other. It needs to have both values otherwise I do not want it to be selected.

Here's a sample of my code:

Code: Select all

SELECT * FROM
    members AS m
    LEFT JOIN clubs AS c ON m.id = c.member_id
    WHERE
        m.first_name = 'Ben'
        AND (c.club_id = '1' AND c.club_id = '3')
 
My code above does not work. I can't use c.club_id = '1' OR c.club_id = '3' either because it will return the result if club_id is set to 1 or 3... I want it to only pull out the rows if one has 1 and the other has 3...

Am I making sense?

Any ideas what code I'd need to accomplish this?

Re: Help with selecting multiple rows from LEFT JOINs

Posted: Mon Jun 02, 2008 8:18 pm
by Benjamin
There may be a better way to do this but I believe this will work for you.

Code: Select all

 
SELECT
  m.*
FROM
  members m
WHERE
  EXISTS(SELECT c1.member_id FROM clubs c1 WHERE c1.member_id = m.id AND c1.club_id = 1)
  AND EXISTS(SELECT c2.member_id FROM clubs c2 WHERE c2.member_id = m.id AND c2.club_id = 3)
 

Re: Help with selecting multiple rows from LEFT JOINs

Posted: Mon Jun 02, 2008 8:19 pm
by Mr Tech
Crazy! There is so much I don't know about MySQL!! Thanks for that. I'll give it a go and see how well it works :)

Re: Help with selecting multiple rows from LEFT JOINs

Posted: Mon Jun 02, 2008 9:05 pm
by Mr Tech
Perfect. Exactly hat I needed. Glad I actually made sense :)