mysql query - record inside another record

Question

table: member

fields:
member_id - unique primary id
voter_id - the member_id of another record

John Doe - member_id = 1, voter_id = 0
ABC Inc. - member_id = 2, voter_id = 1 (John Doe is the voter)

I need to pull all records where the member_id never appears in the voter_id field. In this case, I need it to find member_id 2, but not member_id 1.

Thanks guys. I looked but didn't find the answer here.

Answer 1

There's LOTS of ways to do this.. here's a few. Subselect

SELECT * 
FROM  member 
WHERE member_ID not in (select voter_ID from member)

JOIN

SELECT  * 
FROM member ma
LEFT join member mb on mb.voter_Id = ma.member_Id and Mb.meber_ID is null

EXISTS

SELECT Member_ID, Voter_ID 
FROM member MA 
WHERE not exists (Select 1 from member MA where MA.Member_ID = MB.Voter_ID)

As far as which to select depends on lots of things but here's one opinion

Answer 2

Try a missing match self join:

SELECT m1.member_id
FROM member m1
LEFT JOIN member m2 ON m2.voter_id = m1.member_id
AND m2.member_id IS NULL

Answer 3

SELECT * from member WHERE member_id NOT IN (SELECT voter_id FROM member);