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
Comments
Post a Comment