CODEWITHSUNDEEP
postgresql
superman72
superman72

Reputation: 3

How can I do an IF ELSE statement on POSTGRES JOIN query without joining more than once?

I would like to join table_b on table_a conditionally where it first tries to join on animal_name, then person_name, then city_name, and finally, it doesn't join. However, if a match is found on animal_name, I don't want to double join on person_name. Here is the example:

table_a:

[id, animal_name, person_name, city_name]

1    dog            tom         ny
2    cat            joe         sf
3    frog           ron         la
4    duck           ben         ri

table_b:

[name, noise]

dog     woof
joe     hello
ny      honk

I'd like to something like this:

SELECT * FROM table_a
IF
    LEFT JOIN table_b on table_b.animal_name = table_a.name
ELSE IF 
    LEFT JOIN table_b on table_b.person_name = table_a.name
ELSE IF 
    LEFT JOIN table_b on table_b.city_name = table_a.name

And the result should look like this

[id, animal_name, person_name, city_name, noise]

1       dog         tom         ny          woof
2       cat         joe         sf          hello
3       frog        ron         la          honk

Upvotes: 0

Views: 229

Answers (2)

Motoralfa
Motoralfa

Reputation: 450

You can try something like this:

LEFT JOIN table_b ON (table_a.animal_name = table_b.name) OR (table_a.person_name = table_b.name) OR (table_a.city_name = table_b.name)

Upvotes: 0

fphilipe
fphilipe

Reputation: 10054

Note, your example doesn't make sense:

  • It should be la instead of ny to get the desired output.
  • The joins in your attempt have table_a and table_b reversed.

This should do it:

SELECT
    a.*,
    COALESCE(b1.noise, b2.noise, b3.noise) AS noise
FROM table_a AS a
LEFT JOIN table_b AS b1 ON b1.name = a.animal_name
LEFT JOIN table_b AS b2 ON b2.name = a.person_name
LEFT JOIN table_b AS b3 ON b3.name = a.city_name
WHERE COALESCE(b1, b2, b3) IS NOT NULL
ORDER BY a.id;

Upvotes: 1

Related Questions