xendi xendi - 1 year ago 70
MySQL Question

MySQL select rows that do not have matching column in other table

I can't seem to figure this out so far. I am trying to join two tables and only select the rows in table A that do not have a matching column in table B. For example, lets assume we have a users table and a sent table.

users
table has the following columns:
id, username


sent
table has the following columns:
id, username


I want to select all rows from
users
where
username
does not exist in
sent
table. So, if
tom
is in
users
and in
sent
he will not be selected. If he is in
users
but not in
sent
he will be selected. I tried this but it didn't work at all:

SELECT pooltest.name,senttest.sentname
FROM pooltest,senttest
WHERE pooltest.name != senttest.sentname

Answer Source

Try this SQL:

SELECT users.username
FROM  users
LEFT JOIN sent ON sent.username = users.username
WHERE sent.username IS NULL;

The better way in my opinion would be:

SELECT users.username
FROM  users
LEFT JOIN sent ON sent.id = users.id
WHERE sent.id IS NULL;

As both the id fields, would be indexed (primary key I would have thought) so this query would be better optimised than the first one I suggested.

However you may find my first suggestion better for you, it depends on what your requirements are for your application.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download