CODEWITHSUNDEEP
mysql
igluratds
igluratds

Reputation: 5

MYSQL query to return rows that are NOT in a set

I have two tables:

Contact (id,name) Link (id, contact_id, source_id)

I have the following query which works that returns the contacts with the source_id of 8 in the Link table.

SELECT name FROM `Contact` LEFT JOIN Link ON Link.contact_id = Contact.id WHERE Link.source_id=8;

However I am a little stumped on how to return a list of all the contacts which are NOT associated with source_id of 8. A simple != will not work as contacts without any links are not returned.

Thanks.

Upvotes: 0

Views: 82

Answers (5)

mcabral
mcabral

Reputation: 3558

Shouldnt this work?

WHERE Link.source_id <> 8 OR Link.source_id IS NULL

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332551

Use:

   SELECT c.name 
     FROM CONTACT c
LEFT JOIN LINK l ON l.contact_id = c.id
                AND l.source_id = 8
    WHERE l.contact_id IS NULL

Upvotes: 1

codaddict
codaddict

Reputation: 454970

You can change the where condition from:

Link.source_id = 8;

to

Link.source_id != 8;

You can also use <> in place of !=

Both are the not equal operator in MySQL

Upvotes: 0

dkretz
dkretz

Reputation: 37645

There's a straight-forwarrd way to do it just as you expressed it.

SELECT name FROM .... WHERE Link.source_id != 8;

Upvotes: 0

Justin Ethier
Justin Ethier

Reputation: 134167

Just say WHERE Link.source_id != 8;

Upvotes: 2

Related Questions