[SOLVED] MySQL for joining a table on uuid and omitting uuids present in both table

MySQL for joining a table on uuid and omitting uuids present in both table

I'm trying to figure out how best to write a statement that omits returning rows (maintable) that a user (uuid_user) has already seen (seentable). Rows that a user has already seen are stored in seentable. Here is a simplified version of my tables. uuid is used as the key to join. uuid_user identifies the particular user's seen rows.

Not quite working:

SELECT * 
  FROM maintable 
  JOIN seentable 
    on maintable.uuid = seentable.uuid 
 WHERE maintable.uuid != seentable.uuid

maintable
uuid (char36)
lng,lat (POINT)
timestamp (timestamp)

seentable 
uuid (char36)
uuid_user (char36)
timestamp (timestamp)

Solution

If you want to stick with a join approach, then you can use a left anti-join:

SELECT m.*
FROM maintable m
LEFT JOIN seentable s ON m.uuid = s.uuid
WHERE s.uuid IS NULL;

Or, you could phrase this using exists logic:

SELECT m.*
FROM maintable m
WHERE NOT EXISTS (SELECT 1 FROM seentable s WHERE s.uuid = m.uuid);