CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Jeff G
Jeff G

Reputation: 4677

Postgres Select from a Table Based On Query Result

I have two tables with identical columns, in an identical order. I have a desire to join across one of the two tables, depending on a subquery condition. For example, assume I have the following schema:

CREATE TABLE b (
    bid SERIAL PRIMARY KEY,
    cid INT NOT NULL
);

CREATE TABLE a1 (
    aid SERIAL PRIMARY KEY,
    bid INT NOT NULL REFERENCES b
);

CREATE TABLE a2 (
    aid SERIAL PRIMARY KEY,
    bid INT NOT NULL REFERENCES b
);

I would like a query, that performs a join across either a1 or a2 based on some condition. Something like:

WITH z AS (
  SELECT cid, someCondition FROM someTable
)
SELECT *
FROM CASE z.someCondition THEN a1 ELSE a2 END
JOIN b USING (bid)
WHERE cid = (SELECT cid FROM z);

However, the above doesn't work. Is there some way to conditionally join across a1 or a2, depending on some boolean condition stored in table z?

Upvotes: 2

Views: 6067

Answers (3)

wildplasser
wildplasser

Reputation: 44250

If the conditions are exclusive (I expect they are): just do both queries and UNION ALL them, with the smart union construct:

WITH z AS (
  SELECT cid
        , (cid %3) AS some_condition -- Fake ... 
        FROM  b
  )
SELECT *
  FROM a1
  JOIN b USING (bid)
 WHERE EXISTS( SELECT * FROM z
        WHERE some_condition = 1 AND cid = b.cid )
UNION ALL
SELECT *
  FROM a2
  JOIN b USING (bid)
 WHERE EXISTS( SELECT * FROM z
        WHERE some_condition = 2 AND cid = b.cid )
        ;

A somewhat different syntax to do the same:

WITH z AS (
  SELECT cid
        , (cid %3) AS some_condition 
        FROM  b
)
SELECT *
  FROM a1
  JOIN b ON a1.bid = b.bid
        AND EXISTS( SELECT * FROM z
        WHERE some_condition = 1 AND cid = b.cid )
UNION ALL
SELECT *
  FROM a2
  JOIN b ON a2.bid = b.bid
        AND EXISTS( SELECT * FROM z
        WHERE some_condition = 2 AND cid = b.cid )
        ;

Upvotes: 5

Patrick
Patrick

Reputation: 32199

If, like in your example, you have only a few columns that you want to output, you can use the CASE statement for every column:

SELECT CASE z.someCondition THEN a1.aid ELSE a2.aid END AS aid,
       CASE z.someCondition THEN a1.bid ELSE a2.bid END AS bid
FROM b
JOIN a1 ON a1.bid = b.bid
JOIN a2 ON a2.bid = b.bid
JOIN someTable z USING (cid);

Depending on the size of tables a1 and a2 and how many columns you have to output, this may or my not be faster than Klin's solution with a function, which is inherently slower than plain SQL and even more so because of the dynamic query. Given that z.someCondition is a boolean value already, the CASE evaluation will be very fast. Small tables + few columns = this solution; large tables + many columns = Klin's solution.

Upvotes: 1

klin
klin

Reputation: 121604

SQL syntax does not allow conditional joins. Probably the simplest way to achieve a similar effect is to use a dynamic query in a plpgsql function, which may look like this:

create function conditional_select(acid int, some_condition boolean)
returns table (aid int, bid int, cid int)
language plpgsql as $$
declare
    tname text;
begin
    if some_condition then tname = 'a1';
    else tname = 'a2';
    end if;

    return query execute format ($fmt$
        select a.aid, b.bid, b.cid 
        from %s a
        join b using(bid)
        where cid = %s;
        $fmt$, tname, acid);
end $$;


select * from conditional_select(1, true)

Upvotes: 3

Related Questions