Sorry about emailing you directly Eddie. I meant to reply to the list with my last email.
Anyway - your suggestion worked wonderfully. Many many thanks. -----Original Message----- From: Edward Peloke [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 15:59 To: MySQL Mailing List (E-mail) Subject: RE: Complex SQL query problem... try a left join select count(*) from jobs left join submissions on jobs.standard_id=submissions.color_id where jobs.standard_id=ID_VALUE and submissions.color_id is null Eddie -----Original Message----- From: Richard Bolen [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 2:37 PM To: MySQL Mailing List (E-mail) Subject: Complex SQL query problem... I'm trying to use a sql query to determine if an ID exists in any of 3 different tables in the database. I need to do this in one SQL query (ideally only using the ID once in the query). I'm using mysql 3.23.47. Here's an example of a query I came up with: select count(*) from jobs, submissions where ID_VALUE in (jobs.standard_id, submissions.color_id) I'm just trying to determine if the ID exists. This query works *IF AND ONLY IF* there is at least one record in each of the tables. If any of the table are empty, this query always returns a count of 0 (even if there is a match in one of the non-empty tables). Does anyone know why this is happening or could someone suggest a alternate query? Thanks, Rich -------------------------------------------------------------------- Rich Bolen Senior Software Developer GretagMacbeth Advanced Technologies Center 79 T. W. Alexander Drive - Bldg. 4401 - Suite 250 PO Box 14026 Research Triangle Park, North Carolina 27709-4026 USA Phone: 919-549-7575 x239, Fax: 919-549-0421 http://www.gretagmacbeth.com/ -------------------------------------------------------------------- --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
