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).

  • Q: Should I have the "seen" table be a separate table for each user?

Currently, I have it as a single seen table for all users

Rows that a user has already seen are stored in seentable for each uuid_user. 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 m 
 LEFT JOIN seentable s 
    on m.uuid = s.uuid 
 WHERE s.uuid IS NULL and s.uuid_user = '[user]'

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

seentable 
uuid (char36)
uuid_user (char36)
timestamp (timestamp)
ina
  • 19,167
  • 39
  • 122
  • 201

1 Answers1

0

You need to move the restriction on uuid_user from the WHERE clause to the ON clause of the join:

SELECT * 
FROM maintable m 
LEFT JOIN seentable s 
    ON m.uuid = s.uuid AND s.uuid_user = '[user]'
WHERE s.uuid IS NULL;

The above is the logic you want here. Now, a successful join between records from the two tables occurs whenever the uuid values match and when the uuid_user value from the seen table matches some value. Note that should the uuid_user value not match for any record, it would not be filtered off, which is what will happen with your current query.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360