mysql - How can I join conditionally based on a value joined to the join? -

i not sure if possible, want records back, attachment, if type (a definition table) 'main' (if has attachment, it's type else, want null.

select r.* record r left join attachment d on d.record_id = r.id left join attachment_type @ on d.type_id = at.id at.name = "main" group r.id 

i redesign of data here, that's not possible. use subquery id before doing join?

i don't know attachment column table looks (beyond fact has type column), should close, meaning (a) it'll return rows, , (b) returned attachment value null if type main:

select   r.*,   case when at.name = 'main' d.whatever else null end attach_thingie record r left join attachment d on d.record_id = r.id left join attachment_type @ on d.type_id = at.id 

and, @freshprinceofso mentioned in comment above, don't see need group by.

one more thing: based on can infer query, don't see glaring design problems among 3 tables. read record can have number of attachment , attachment has (maybe) type. if that's requirements call should ok.

---

addendum: choose maximum of 1 attachment per record based on attachment.id column:

select r.*, d.whatever record r left join   (     select attachment.record_id, max(attachment.id) max_id     attachment     inner join attachment_type @ on attachment.type_id = at.id     at.name = 'main'     group attachment.record_id   ) att on r.id = att.record_id left join attachment d on d.id = att.max_id