MySQL selecting data from 2 tables

Question

I have 2 mysql tables, members and team_members.

members table:

member_id
name

team_members table:

team_id
member_id

I want to select the data from the members table that do not belong to any team (do not exist in team_members table)

                    SELECT *
                    FROM members
                    INNER JOIN team_members
                    ON members.member_id = team_members.member_id

The problem is that the query is doing precisely the opposite i need. It is selecting the data from the members that already have a team. I also tried with '!=' but that gives me all fields duplicated, but does not duplicate the members that belong to a team.

Guffa · Accepted Answer · 2013-09-01 00:25:34Z

3

Use a left join, and select the records where there is no corresponding data from the team_members table:

select m.*
from members as m
left join team_members as t on m.member_id = t.member_id
where t.member_id is null

answered Sep 1, 2013 at 0:25

Guffa

703k111 gold badges760 silver badges1k bronze badges

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

Comments

BellevueBob · Accepted Answer · 2013-09-01 00:27:43Z

1

Try this:

SELECT *
FROM members a
WHERE NOT EXISTS (
  SELECT 1
  FROM team_members b
  WHERE b.member_id = a.member_id
  )

Notice this syntax follows very closely to how you phrased your question. Sometimes SQL can be very easy; but like any language, you need to master the vocabulary.

answered Sep 1, 2013 at 0:27

BellevueBob

9,6345 gold badges32 silver badges56 bronze badges

2 Comments

xRed Over a year ago

yes i was missing that important syntax, just one question, what is the SELECT 1 for ?

BellevueBob Over a year ago

It's just a dummy value to satisfy SELECT syntax; just a placeholder.

Collectives™ on Stack Overflow

MySQL selecting data from 2 tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related