conditional join with input value in postgreslq function

join, postgresql

Solution

Learn to use proper, explicit `JOIN` syntax. Simple rule: Never use commas in the `FROM` clause.

You can do what you want with `LEFT JOIN` and some other logic:

select i.id,
       coalesce(a.letter, g.letter) as letter
from id_table i
left join alphabet_table a
     on i.id = a.id and letter_type = 'alphabet'
left join greek_table g
     on i.id = g.id and letter_type <> 'alphabet'
where a.id is not null or g.id is not null;

The condition using `letter_type` needs to be in the `on` clauses. Otherwise, `alphabet_table` will always have a match.

Problem

I have three tables: ``` create table id_table ( id integer ); insert into id_table values (1),(2),(3); create table alphabet_table ( id integer, letter text ); insert into alphabet_table values (1,'a'),(2,'b'),(3,'c'); create table greek_table ( id integer, letter text ); insert into greek_table values (1,'alpha'),(2,'beta'); ``` I like to create a function that join id_table with either alphabet_table or greek_table on id. The choice of the table depends on an input value specified in the function. I wrote: ``` CREATE OR REPLACE FUNCTION choose_letters(letter_type text) RETURNS table (id integer,letter text) AS $$ BEGIN RETURN QUERY select t1.id, case when letter_type = 'alphabet' then t2.letter else t3.letter end as letter from id_table t1, alphabet_table t2 , greek_table t3 where t1.id = t2.id and t1.id = t3.id; END; $$LANGUAGE plpgsql; ``` I ran `select choose_letter('alphabet')`. The problem with this code is that when id_table joins with alphabet_table, it does not pick up id, No 3. It seems that inner joins are done for both alphabet_table and greek_table (so it only picks up the common ids, 1 and 2). To avoid this problem, I wrote: ``` CREATE OR REPLACE FUNCTION choose_letters(letter_type text) RETURNS table (id integer, letter text) AS $$ BEGIN RETURN QUERY select t1.id, case when letter_type = 'alphabet' then t2.letter else t3.letter end as letter from id_table t1 left join alphabet_table t2 on t1.id=t2.id left join greek_table t3 on t1.id=t3.id where t2.letter is not null or t3.letter is not null; END; $$LANGUAGE plpgsql; ``` Now it pick up all the 3 ids when id_table and alphabet_table join. However, When I ran `select choose_letter('greek')`. The id no. 3 appears with null value in letter column despite the fact that I specified `t3.letter is not null` in where clause. What I'm looking for is that when I ran `select choose_letters('alphabet')`, the output needs to be (1,'a'), (2,'b'),(3,'c'). `select choose_letters('greek')` should produce (1,'alpha'),(2,'beta). No missing values nor null. How can I accomplish this?

Original source