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

join, postgresql, postgresql-8.4, postgresql-9.0, sql

Solution

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

Problem

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` ?

Original source