jwoker Posted January 4, 2011 Share Posted January 4, 2011 Novice here wondering how to get a select query to return a set where there are no records in the joined table.SELECT * FROM job JOIN orders ON job.id = orders.job_id WHERE orders.job_id .....I want to select all from job where there are no corresponding records in orders?Is that possible?Thanks Link to comment Share on other sites More sharing options...
justsomeguy Posted January 5, 2011 Share Posted January 5, 2011 If you used a left join then it would select every record from the job table, and only the matching records from the other table. Records from the job table without a matching record in the other table would have null values in the joined columns.If you want to select only records from the job table that don't have orders, you could do this:SELECT * FROM job WHERE id NOT IN (SELECT job_id FROM orders) Link to comment Share on other sites More sharing options...
jwoker Posted January 5, 2011 Author Share Posted January 5, 2011 How would I write a WHERE clause that tested for null values - what I need is something like WHERE orders.id = NULLI tried that in phpmyadmin and it did not return the set I was looking for... Link to comment Share on other sites More sharing options...
justsomeguy Posted January 5, 2011 Share Posted January 5, 2011 If you only want to return values with missing records, instead of the expensive join use the subquery I added above. Link to comment Share on other sites More sharing options...
jwoker Posted January 5, 2011 Author Share Posted January 5, 2011 OH! I missed the subquery when I first read your post. Thank you, it worked very nicely Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.