If foreign key doesn't exist? How?

[imperium2335]

Hi,

 

I have two tables, and I would like my query to bring back certain rows where a reference doesn't exist in the other table.

 

So basically the opposite to:

 

WHERE invoices.jobRef = jobs.id

 

invoices.jobRef != jobs.id does not work.

[DarkKnight2011]

you could do something like this, but it may use up alot of resources depending on table sizes etc..

 

SELECT * FROM invoices WHERE invoices.jobRef NOT IN (SELECT id FROM JOBS)

 

Im sure there are better ways of doing this, but this should get it working until you find them,

 

to save on resources it may be better to get the list of job id's in one query and then just pass them into this one instead, to save mysql re-running the subquery multiple times

 

Regards

 

DK

[mikosiko]

other option (better IMHO):

 

SELECT invoices.*
    FROM invoices 
    LEFT JOIN jobs ON invoices.jobRef = jobs.id
WHERE jobs.id IS NULL