imperium2335 Posted June 3, 2011 Share Posted June 3, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/238299-if-foreign-key-doesnt-exist-how/ Share on other sites More sharing options...
DarkKnight2011 Posted June 3, 2011 Share Posted June 3, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/238299-if-foreign-key-doesnt-exist-how/#findComment-1224585 Share on other sites More sharing options...
mikosiko Posted June 3, 2011 Share Posted June 3, 2011 other option (better IMHO): SELECT invoices.* FROM invoices LEFT JOIN jobs ON invoices.jobRef = jobs.id WHERE jobs.id IS NULL Quote Link to comment https://forums.phpfreaks.com/topic/238299-if-foreign-key-doesnt-exist-how/#findComment-1224719 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.