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
Typically, you would use NOT EXISTS
for this type of query
SELECT p.Name
FROM pooltest p
WHERE NOT EXISTS (SELECT s.Name
FROM senttest s
WHERE s.Name = p.Name)
An alternative would be to use a LEFT OUTER JOIN
and check for NULL
SELECT p.Name
FROM pooltest p
LEFT OUTER JOIN senttest s ON s.Name = p.Name
WHERE s.Name IS NULL
Note that the implicit join syntax you are using is considered obsolete and should be replaced with an explicit join.