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?
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 )
;