I have a query involving multiple tables that I would like to return in a single query. That means returning multiple sets of the data from the first base table, but that's acceptable for the simplicity in grabbing all the data in one hit.
An example set:
CREATE TABLE t1 (a int, b int, c int); CREATE TABLE t2 (a int, b int, c int); CREATE TABLE base (a int); INSERT INTO t1 (a, b, c) VALUES (1, 1, 111), (1,2,112),(2,1,121),(4,1,141); INSERT INTO t2 (a, b, c) VALUES (1, 1, 211), (2, 2, 222),(5,3,253); INSERT INTO base(a) SELECT * FROM GENERATE_SERIES(1,10);
Now the problem is that I would like to return all the rows from a, but with a single row where t2.b and t1.b match.
CROSS JOIN (SELECT b FROM t1 UNION SELECT b FROM t2 UNION SELECT -1) tmpset LEFT JOIN t1 ON t1.a=base.a AND t1.b=tmpset.b LEFT JOIN t2 ON t2.a=base.a AND t2.b=tmpset.b WHERE t1.a IS NOT NULL
OR t2.a IS NOT NULL
OR (tmpset.b=-1
AND NOT EXISTS (SELECT FROM t1 WHERE t1.a=base.a)
AND NOT EXISTS (SELECT FROM t2 WHERE t2.a=base.a)
);
but this seems like a really convoluted way to do it.
Is there a join style that will return the set I want without the pain?
I should be clear that the real world data is much more complex than this, but it describes the basic problem.