mysql query: select related record depending on column values

I'm having a hard time wrapping my mind around a query for this scenario:

tbl1 and tbl2 are left joined i want to select the record from tbl2 based on what multiple records may exist.开发者_开发知识库

tbl2 has columns A and B. I need to select the record where: A = 0 and B = 1 or A = 1 and no related record where B = 1 exists

the result should only have one record from tblB -- so a straight "OR" doesn't work. the criteria is based on the potential existence of another record.

the result should only have one record from tblB -- so a straight "OR" doesn't work.

Actually, it does. left join ... on A = 0 and B = 1 or A = 1 and B is null. What you then need to do is eliminate the duplicates with the distinct operator.