Try this (not sure the syntax is exactly right since I'm more used to Oracle's outer join syntax):
select * from main left join activity on main.user = activity.id where activity.id is null; This should return only the users in main that are not in activity. --Walt Weaver Bozeman, Montana -----Original Message----- From: Steve Davies [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 10, 2002 12:23 PM To: [EMAIL PROTECTED] Subject: Selecting data from one table if it's NOT in another > > >Hi all > >There's probably a simple answer to this but I can't find it and it's >doing my head in. > >I have two tables: >main contains user data, address,name,etc. >activity contains user activity > >'user' in main has the same value as 'id' in activity > >I want to produce a list of users who have not been active, i.e. pull >all the data for users in main who do NNOT appear in activity > >I've tried running the following sql > >select * from main as t1, activity as t2 where t1.user != t2.id; >select * from main left join activity on main.user = activity.id >select @id:=id from activity; select * from main where user != @id; > >But each query produces near identical results - more or less a dump of main. > >Any ideas gratefully rec'd. > >Thanks > >Steve Davies > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
