[timmerk]

I have 2 tables, with these schemas:

table: sites
columns: id, name

table: pending_lists
columns: id, user_id, site_id

I'm trying to write a SQL statement to return all the sites.names as long as they are not listed in the 'pending_lists' table for a particular user (user 1 in this case.)

So essentially, once the user gets added to the 'pending_lists' table (they get added when they select a site.name), they won't see the site.name that they just ever again. They will only see the site.names that they have never selected.

This is what I have so far:

Code:
SELECT sites.id, sites.name
	FROM sites, pending_lists
	WHERE pending_lists.user_id = 1
	AND pending_lists.site_id != sites.id

The problem is that I want it to return all of the site.names if the user has 0 entries in the 'pending_list' table. At least one of the problems in the statement is this part: 'WHERE pending_lists.user_id = 1' because that will make it so the user has to be in the 'pending_lists' table if anything is to be returned. I can't remove that part either, because the sites.names returned are specific to the user.

Thanks for any help!

[MacAtak]

If you have MySQL version 4.1 or above you can use a sub select as follows:

Code:
SELECT a.id, a.name
FROM sites a
WHERE a.id NOT IN (SELECT b.site_id
             FROM pending_lists b
             WHERE b.user_id = 1)

Otherwise this should work:

Code:
SELECT a.id, a.name
FROM sites a
LEFT OUTER JOIN pending_lists b
ON a.id != b.site_id
WHERE b.site_id IS NULL
AND b.user_id = 1

A left outer join will select all rows from the table on the left, in this case sites, and join it with that data in the right table, in this case pending_lists. If there is no match with the right table it will just return NULL values.


I can't test these but I think they should work. Let me know how it goes.

[timmerk]

It seems to work (the first one), thanks!