how about just change the WHERE clause to : WHERE reports_signup.user_id != '2' (or whatever user_id)
ie: does not equal. That should grab all reports they are not signed up for and the left join will include all reports that nobody is signed up for (left-overs). Another issue is that you're original query will not perform as you say. ie: It will not get all reports they are signed up for (only). It will also get the reports that nobody is signed up for. This should change to a basic join like so: SELECT reports.id,reports.report_name FROM reports, reports_signup ON reports.id = reports_signup.report_id WHERE reports_signup.user_id = '2'; AND the query to find reports they are NOT signed up for (assumming this includes the sibset - reports nobody is signed up for) is : SELECT reports.id,reports.report_name FROM reports LEFT JOIN reports_signup ON reports.id = reports_signup.report_id WHERE reports_signup.user_id != '2'; Regards ... -----Original Message----- From: Peter Hicks [mailto:[EMAIL PROTECTED]] Sent: Sunday, 17 March 2002 9:28 PM To: [EMAIL PROTECTED] Subject: Emulating a subselect Hi everyone Maybe somebody can help me with what seems like a tricky subselect-avoidance question? In a MySQL database, there are two tables - 'reports' and 'reports_signup'. 'reports' has two columns, 'id' and 'report_name'. 'reports_signup' has three columns - 'id', 'report_id' and 'user_id'. ('id' in this table may be superfluous). 'report_id' is a value from the 'reports_table', and 'user_id' is a value from another table. I've written a query to select the report names of all reports a particular user (say, id '2') is signed up to: SELECT reports.id,reports.report_name FROM reports LEFT JOIN reports_signup ON reports.id = reports_signup.report_id WHERE reports_signup.user_id = '2'; I now want to write a query to select the report names of all reports a particular user is *not* signed up to - I'm having big troubles with this. Can anybody assist me? Best wishes, Peter. --------------------------------------------------------------------- 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
