Join multiple tables without duplicated results and without UNION in PostgreSQL Lets assume we have two tables A and B in PostgreSQL:
A




title
first_group_id
second_group_id




My Title
1
2



B




group_id
element_name




1
First


1
Second


1
Third


2
Fourth


2
Fifth




I want to get the following result:




A.title
B0.element_name
B1.element_name




My Title
First



My Title
Second



My Title
Third



My Title

Fourth


My Title

Fifth




If I query something like the following - I will get 6 rows instead of 5 cause all rows from B0 will be mixed with all rows from B1 and the number of result rows will grow in progression if B0 or B1 have more rows:
SELECT A.title, B0.element_name, B1.element_name
LEFT JOIN B B0 ON A.first_group_id = B0.group_id
LEFT JOIN B B1 ON A.second_group_id = B1.group_id
FROM A;





A.title
B0.element_name
B1.element_name




My Title
First
Fourth


My Title
First
Fifth


My Title
Second
Fourth


My Title
Second
Fifth


My Title
Third
Fourth


My Title
Third
Fifth




I can achieve the desired behavior by using UNION but I wonder if there is a more efficient way to do that? Especially when it's required to make more than two JOINs in the same manner.
One neat option to avoid querying the tables twice is to unpivot A first.
You need some CASE expressions because you also want to split out element_name back into different columns.

Not so good at Postgres, I may have the syntax slightly off

SELECT
    A.title,
    CASE WHEN A.first_group_id  = v.group_id THEN B.element_name END AS element_name0,
    CASE WHEN A.second_group_id = v.group_id THEN B.element_name END AS element_name1
FROM A
INNER JOIN LATERAL (VALUES
    (A.first_group_id),
    (A.second_group_id)
) AS v(group_id)
LEFT JOIN B ON v.group_id = B.group_id;