Attach a table with one table if condition1 is true and with another table if condition1 is false?

Question

I have the following tables:

User_Group id group_id group_type ------------------------ 1 100 A 1 100 B 2 101 B 2 102 A Group_A id name --------- 100 A 101 B 102 C Group_B id name --------- 100 D 101 E 102 F 

I want the group names of all users (using array.agg() ). We should get the name of the group from group A, if user group type = A and group B, if user group type = B. The result should be:

 userid groups -------------- 1 A,D 2 E,C 

I created a fiddle for this and gave a solution using a union of 2 separate requests. Is it possible to do this without combining, something in which I can decide on which table to select the group name from one connection in user_groups , group_A and group_B ?

Answer 1

 select ug.id, array_agg( case ug.group_type when 'A' then g_a.name when 'B' then g_b.name else 'N/A' end) from user_groups ug left outer join group_A g_a on ug.group_id = g_a.id left outer join group_B g_b on ug.group_id = g_b.id group by ug.id 

SQL script example

Answer 2

You can do this without uniting with leftist unions (I would advise to use explicit association anyway as implicit association for 20 years out of date Aaron Bertrand wrote a good blog on why ). Group_Type can become a join condition, meaning that the table is joined only if the correct group type is found:

 SELECT ug.ID, ARRAY_AGG(COALESCE(a.Name, b.Name)) FROM User_Groups ug LEFT JOIN group_A a ON a.ID = ug.group_ID AND ug.Group_Type = 'A' LEFT JOIN group_B b ON b.ID = ug.group_ID AND ug.Group_Type = 'B' WHERE COALESCE(a.ID, b.ID) IS NOT NULL -- ENSURE AT LEAST ONE GROUP IS MATCHED GROUP BY ug.ID; 

However, I would be inclined to use UNION Still, but move it like this:

 SELECT ug.ID, ARRAY_AGG(Name) FROM User_Groups ug INNER JOIN ( SELECT 'A' AS GroupType, ID, Name FROM Group_A UNION ALL SELECT 'B' AS GroupType, ID, Name FROM Group_B ) G ON g.GroupType = ug.Group_Type AND g.ID = ug.Group_ID GROUP BY ug.ID; 

Added your script with my requests