Ignore records I've "skipped"?

Question

I have two tables: tableA and tableB

tableA
------
id
...

tableB
------
id
tableA_id
user_id

If a user doesn't have enough information to process an item they "skip" it; this adds a row to tableB with the id of the item from tableA and their user id.

I want to get rows from tableA that a user has not skipped, but other users might have.

For example:

userA enters the queue
userA is assigned item1
userA skips item1

userB enters the queue
userB is assigned item1
userB skips item1

userA enters the queue
userA is assigned item2

userB enters the queue
userB is assigned item3

userC enters the queue
userC is assigned item1

So far I have:

SELECT *
FROM tableA
LEFT OUTER JOIN tableB ON tableA.id = tableB.tableA_id
WHERE tableB.user_id IS NULL OR tableB.user_id != %s
GROUP BY tableA.id
;

This returns item1 for all other users after it is skipped by any user because user_id is no longer NULL. This prevents other users from skipping the item.

How do I accomplish what I'm trying to do?

Answer 1

Try this:

select * from tableA
where tableA.id not in
(select tableB.tableA_id from tableB where tableB.user_id = %s)