Select id which is not found in 2 other tables?

[EchoFool]

Hey - i was curious what MYSQL function i should use to get ids from one table which is not found in 2 other tables? I tried this:

 

SELECT RecordID 

FROM states 

WHERE 

RecordID NOT IN
(SELECT RecordID FROM trade WHERE (StateID1='$Info[12]' OR StateID2='$Info[12]') AND (StateID1=states.RecordID OR StateID2=states.RecordID))
                    
AND RecordID NOT IN
(SELECT RecordID FROM businesses WHERE (StateID1='$Info[12]' OR StateID2='$Info[12]') AND (StateID1=states.RecordID OR StateID2=states.RecordID))

 

How ever this is giving me all ids which are found in at least one of the tables from the "NOT IN" part.

 

How do i make a query to fine ids not found in the other two tables ?

 

[kickstart]

Hi

 

Try something like this.

 

SELECT a.RecordID
FROM states a
LEFT OUTER JOIN (SELECT RecordID FROM trade WHERE (StateID1='$Info[12]' OR StateID2='$Info[12]')) b
ON a.RecordID = b.RecordID
LEFT OUTER JOIN (SELECT RecordID FROM businesses WHERE (StateID1='$Info[12]' OR StateID2='$Info[12]')) c
ON a.RecordID = c.RecordID
WHERE b.RecordID IS NULL
AND c.RecordID IS NULL

 

All the best

 

Keith

[EchoFool]

Thanks !