great! hope it works ! -----Original Message----- From: Richard Bolen [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 9:17 AM To: MySQL Mailing List (E-mail) Cc: Edward Peloke Subject: RE: Complex SQL query problem...
FYI - this query seemed to work. select * from nodes left join nodes as n2 on n2.parent_id = nodes.node_id left join jobs on jobs.parent_id = nodes.node_id left join colors on colors.parent_id = nodes.node_id where nodes.node_id = ? and ((n2.parent_id is not NULL) or (jobs.parent_id is not NULL) or (colors.parent_id is not NULL)) I need to do some more testing to be sure. Rich -----Original Message----- From: Edward Peloke [mailto:[EMAIL PROTECTED]] Sent: Monday, September 23, 2002 08:46 To: Richard Bolen Subject: RE: Complex SQL query problem... After I sent this it hit me that it may not work if the first table (jobs) contained no rows...I believe this would only work if the tables left joined were empty not the jobs table. sorry... I apologize did not respond Friday but I left work at 4. Eddie -----Original Message----- From: Richard Bolen [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 4:19 PM To: Edward Peloke Subject: RE: Complex SQL query problem... Does this handle the case where the ID is in the submissions table but not the jobs table? How would this look if there was a third table also? Thanks again for you help! Rich -----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 --------------------------------------------------------------------- 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
