combination join against multiple tables

From: Geoff Winkless <pgsqladmin(at)geoff(dot)dj>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: combination join against multiple tables
Date: 2020-01-31 14:01:17
Message-ID: CAEzk6fcrf9xh9+1sqx9hrgqW3fURNkLUxftJk0_vS_d=n=oPbA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

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.

So the results I would like:

a | c | c
----+-----+-----
1 | 111 | 211
1 | 112 |
2 | 121 |
2 | | 222
3 | |
4 | 141 |
5 | | 253
6 | |
7 | |
8 | |
9 | |
10 | |

At the moment I'm doing

SELECT base.a, t1.c, t2.c
FROM base
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.

Thanks

Geoff

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2020-01-31 15:25:07 Re: combination join against multiple tables
Previous Message Durumdara 2020-01-31 13:43:41 Re: Add column with default value in big table - splitting of updates can help?