mysql - Not getting all records from Table A in left join -

mysql - Not getting all records from Table A in left join -

i have table of quality assurance statuses recipes , want select 'discard' records, including info 'perfect' statuses overlap. however, i'm getting intersection , want 'discard' records.

i want perform left bring together give me 'discard' rows recipe_qa table, joined 'perfect' rows.

select * recipe_qa bad left bring together recipe_qa on good.id = bad.id bad.type = 'discard' , good.type = 'perfect'

the query above returning rows there 'perfect' , 'discard' record (24 rows) id. want query give me 'discard' rows (76 rows), either 'perfect' id or null there no corresponding row.

here's fiddle: http://sqlfiddle.com/#!2/faa49/4

what doing wrong?

when working left (or outer) joins have consider when info limit imposed vs when cartesian created.

say want records 1 table (a) , match in other (b). when bring together executed, null values nowadays in b (unmatched records a). adding limiting criteria (where clause) against b fields, in-fact eliminate records want a; clause executes after join. has same effect if started inner join!

this situation can avoided moving limiting criteria on b table bring together when using outer joins. way cartesian generated after limit has been imposed ensuring "all records" table remains records. if not on join, you've seen, limit imposed after cartesian , null values removed, , no longer "all records" left bring together negated. it's if doing inner bring together in first place. why doing or statement homecoming nulls , value works if not null column (or type of null has no semantic meaning) lc points out in comments below.

in case clause of good.type eliminating results of left bring together either add together criteria bring together forces limits before cartesian generated (allowing nulls exist)

select * recipe_qa bad left bring together recipe_qa on good.id = bad.id , good.type = 'perfect' bad.type = 'discard'

http://sqlfiddle.com/#!2/faa49/8/0

or utilize null status not exclude records left join. has risks indicted in below comments.

select * recipe_qa bad left bring together recipe_qa on good.id = bad.id bad.type = 'discard' , (good.type = 'perfect' or good.type null)

mysql sql join left-join