Help with selecting multiple rows from LEFT JOINs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Help with selecting multiple rows from LEFT JOINs

Post 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?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Help with selecting multiple rows from LEFT JOINs

Post 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)
 
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Help with selecting multiple rows from LEFT JOINs

Post 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 :)
User avatar
Mr Tech
Forum Contributor
Posts: 424
Joined: Tue Aug 10, 2004 3:08 am

Re: Help with selecting multiple rows from LEFT JOINs

Post by Mr Tech »

Perfect. Exactly hat I needed. Glad I actually made sense :)
Post Reply