Or use an outer join with "where members.list_id is null", as was mentioned on the list earlier today.
--Walt > -----Original Message----- > From: Martijn Tonies [mailto:[EMAIL PROTECTED] > Sent: Friday, February 06, 2004 3:18 PM > To: [EMAIL PROTECTED] > Subject: Re: A challenge I think > > > Hi, > > > Using MySQL 4x > > I have two tables > > Lists and members > > Now for every list that a member is a member of there is > list ID in the > > members table so to query a members lists I would do the following > > SELECT members.EmailAddr, members.ListID > > > > FROM members INNER JOIN lists ON members.ListID = lists.ListID > > WHERE (((members.EmailAddr)="[EMAIL PROTECTED]")); > > Loose the parenthesis - no need. > > > So I get say > > [EMAIL PROTECTED] 3 > > [EMAIL PROTECTED] 2 > > [EMAIL PROTECTED] 44 > > > > And it works fine, but now I want to find which lists I'm > not a member of > > and would appreciate some help. > > select l.listid from lists l where l.listid not in (select > m.listid from > members m > where m.emailaddr = '[EMAIL PROTECTED]') > > With regards, > > Martijn Tonies > Database Workbench - developer tool for InterBase, Firebird, > MySQL & MS SQL > Server. > Upscene Productions > http://www.upscene.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
