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
