MySQL select rows that do not have matching column in other table
duplicates, join, mysql, unique
Solution
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.
Problem
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 ```