MySQL Left Join with multiple rows

Question

I'm looking to join a 2 tables but the second table has a one to many relation. Can I omit the entire row if any of the lines have a certain value? Let me explain more.

User table

id name email
1  bob  [email protected]
2  foo  [email protected]


Music table

id userId
1  1
1  2
2  1
3  1
2  2

Say I don't want it to show the user if he has a relation to music table id 2. Also looking for distinct user.

If I try something like this it will still show both users.

SELECT * FROM users u LEFT JOIN music m ON u.id = m.userId WHERE m.id <> 3

I want it to check all the rows and if it has the id 3, it won't show. I hope I made sense. Thanks a lot.

Have you tried adding it to your ON condition? ON u.id=muserId and m.id <> 1 — Adam Plocher
– Adam Plocher, Commented Nov 28, 2012 at 3:01
I just did but it's returning the same set. But I appreciate the suggestion. — Hass
– Hass, Commented Nov 28, 2012 at 3:06
Your query should return only 1 row with user foo so where's the problem? — SubRed
– SubRed, Commented Nov 28, 2012 at 3:12
@SubRed Sorry about that but the table might not have been clear. I adjusted that. See right now if I don't want to join the user if he has a music id of 3, it will still show up because he has an id of 1 and 2. — Hass
– Hass, Commented Nov 28, 2012 at 3:17

SubRed · Accepted Answer · 2012-11-28 03:24:52Z

1

Try using sub query like this:

SELECT * FROM users
WHERE id NOT IN (SELECT userId FROM music WHERE id=3)

This query means to select all users if their id is not related with music.id 3.

answered Nov 28, 2012 at 3:24

SubRed

3,1871 gold badge21 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hass Over a year ago

Perfect! Sorry for the late response, my query was a little bit more complicated than this so I was trying to match it but it worked perfectly. Thank you so much.

Collectives™ on Stack Overflow

MySQL Left Join with multiple rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related