Join 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 have to get the group name from group A if the user's group type = A and from group B if the user's group type = B. The result should be :

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

I have created a fiddle for this, and given a solution using union of 2 separate queries. Can it be done without the union, something in which I can decide on which table to pick the group name from with a single join of user_groups, group_A and group_B ?

score 8 · Accepted Answer · answered Oct 23 '12 at 14:30

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 Fiddle Example

score 6 · Answer 2 · edited Jul 29 '20 at 18:25

You can do this without union using left joins (I'd advise using explicit joins anyway since implicit joins are 20 years out of date Aaron Bertrand has written a good blog as to why). The Group_Type can become a join condition meaning the table is only joined when the right 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 a UNION Still, but move it as follows:

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;

Your Fiddle with my queries added

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

2 Answers2