KnowledgeHub
Questions
Tags
Users
Search
Alex Rivera
|
Logout
Edit Question
Title
Body
Using MySQL 5.x I want to efficiently select all rows from table X where there is no related row in table Y satisfying some condition, e.g. Give me all records in X where a related Y with foo = bar does NOT exist SELECT count(id) FROM X LEFT OUTER JOIN Y ON y.X_id = X.id AND y.foo = 'bar' WHERE y....? As I understand it, a left outer join is guaranteed to produce a row for each row in the left (first) table -- X in this case -- whether or not a satisfying row in the joined table was found. What I want to do is then select only those rows where no row was found. It seems to me that y.X_id should be NULL if there is no matching record, but this test doesn't seem to work. Nor does y.X_id = 0 or !y.X_id. Edits : corrected transcription error (ON not AS) which was pointed out by several responses. Fixed grammatical error.
Tags (comma-separated)
Save Edits
Cancel