0

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)
ina
  • 19,167
  • 39
  • 122
  • 201

1 Answers1

6

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);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • interesting, did not know there is sucha thing as an anti-join! – ina Feb 21 '21 at 22:07
  • Ah, actually this doesn't quite work - since if a user hasn't seen a particular row from maintable, it won't be in seentable. (sorry updated the table to include uuid_user in seentable) – ina Feb 22 '21 at 01:31
  • Or should I have a new "seen" table of for each user? – ina Feb 22 '21 at 01:32
  • I have reverted your question, and you should ask a new one. If you really feel the need to edit this way, you should at least include sample data, which you did not do. – Tim Biegeleisen Feb 22 '21 at 01:50
  • https://stackoverflow.com/questions/66309124/mysql-omitting-rows-a-user-has-already-seen-from-checking-a-seen-table – ina Feb 22 '21 at 02:01