How to exclude records in one table based on an entry in another

I’m working with another developer to implement a ‘memo book’ into an application. I’m fairly new to semi-advanced SQL and could use a little help.

Here’s the deal:

I have a table called ‘memos’ which contains the fields memo_id, sender, date_sent, subject, and memo_text. The memo_id field is an automatically generated unique integer that is used to identify a specific memo in the table.

I have another table called ‘memo_statuses’ which contains two fields: memo_id and user_id.

When a user accesses a record (memo) in the memos table, that memo_id and the users user_id is inserted into the memo_status table. This marks that specific memo as read. So the memo_id in the memo_statuses table is simply copied from the memo_id of the memos table.

Pretty simple.

Now here is where I’m a bit lost: When I load a list of memos (taken from the memos table), I want to exclude all of the records that the logged in user (identified with user_id) has ‘read’ (i.e: has an entry in the memo_statuses table. This is where I get lost. I’m sure it’s some sort of join but I’ve never used them so I need help.

Can anyone offer me advice?

SELECT * FROM TABLE1 a WHERE NOT EXISTS(SELECT * FROM TABLE2 b WHERE a.userid=b.userid and b.has_read=true)

if I understood your description

Another option

SELECT * FROM memos LEFT OUTER JOIN memo_statuses ON memos.memo_id = memo_statuses.memo_id WHERE memo_statuses.user_id IS NULL;

[quote=295739:@Dave S] SELECT * FROM TABLE1 a WHERE NOT EXISTS(SELECT * FROM TABLE2 b WHERE a.userid=b.userid and b.has_read=true)
if I understood your description[/quote]

Worked great! As per your instructions (and with a bit of input from someone else) I came up with the following SQL statement:

SELECT memos.* FROM memos memo WHERE NOT EXISTS (SELECT NULL FROM memo_statuses read_memos WHERE read_memos.user_id = memo.user_id AND read_memos.username = <the session stored username>)

This seems right and it works as expected. Can you see any problems with it?