MySQL Forums
Forum List  »  Newbie

Find items that are in one table but not the other?
Posted by: Matthew Pye
Date: May 26, 2005 02:26PM

I'm trying to extract a list of DVDs which the user doesn't have from a database.

3 tables:
usersdvds
dvds
users

usersdvds and dvds have a matching key dvdid
usersdvds and users have a matching key userid

usersdvds only contains an entry if the user has the dvd (or has requested it).

I can extract the list of dvds the user has like this:
SELECT * FROM dvd,usersdvds,users WHERE usersdvds.userid = users.userid AND usersdvds.dvdid = dvd.dvdid AND username='$logged_in_username' AND status='1'

I can extract the list of dvds the user has requested like this:
SELECT * FROM dvd,usersdvds,users WHERE usersdvds.userid = users.userid AND usersdvds.dvdid = dvd.dvdid AND username='$logged_in_username' AND (status='2' OR status='3')

How can I extract the dvds they don't have? There is nothing to match it up to in the usersdvds table other than the fact there is nothing in that table,

Any help appreciated.

Options: ReplyQuote


Subject
Written By
Posted
Find items that are in one table but not the other?
May 26, 2005 02:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.