I have a rather complex mysql problem. I have a table like this:

Please look at it at imgur

So I want to join the second table depending on the value of sink_node_type to different other tables. the result should be something like

project.name sink_type_a.name sink_type_b.name.

I have tried something like:

select p.name, sa.name, sb.name
from project p
join node n on p.id = n.project_id
join sink_type_a sa on n.sink_id = sa.id and n.sink_type = 'a'
join sink_type_b sb on n.sink_id = sb.id and n.sink_type = 'b';

But while it works for a join with only one of the sink_type tables, the result set is empty if I try both. Seems both "and" clauses are considered at the same time and therefore exclude each other? Is there a way to do a join like this? (In reality I have 6 of the sink_type tables)

Thank you very much for every hint!

CodePudding user response：

Use LEFT JOIN. When you join with the table sink_type_b using a JOIN, all rows of nodes that are not of type b will be removed from the result. Using LEFT JOIN allows you to return all rows on the left side of the join. sa.name will be NULL when this is a sink of type b, and sb.name will be NULL for a sink of type a.

SELECT p.name, sa.name, sb.name
FROM project p
JOIN node n ON p.id = n.project_id
LEFT JOIN sink_type_a sa ON n.sink_id = sa.id AND n.sink_type = 'a'
LEFT JOIN sink_type_b sb ON n.sink_id = sb.id AND n.sink_type = 'b';