SQLite Forum

joining tables on multiple conditions; how to do it when second table lacks some rows?
Login

joining tables on multiple conditions; how to do it when second table lacks some rows?

(1) By anonymous on 2020-10-09 17:14:16 [source]

Dear all,

may I ask for some help on a query which I don't know how to express.

Basically, I have a view "resultmatrix" defined as

SELECT s.value AS mp, g.groupname AS groupname, q.name AS quantity, q.sumrule AS sumrule FROM generate_series(1,403) AS s, (SELECT DISTINCT groupname FROM "group") g, (SELECT * FROM quantity WHERE unit='1' GROUP BY name, unit, sumrule) q ORDER BY sumrule ASC, groupname ASC, mp ASC;

It returns all combinations of 403 "mp"s, 18 different "groupname"s, 1 "quantity" and 2 different "sumrule"s (TRUE or FALSE).

A 2nd view named "tracesum" basically returns "mp, groupname, quantity, sumrule, value" (and some more columns, but they don't matter here). It does not necessarily contain all combinations. For example, there might be no row with mp=73 at all, or only sumrule=TRUE for mp=209 (but not sumrule=FALSE).

My task is to "complete" all missing combinations with NULLs. So what I'd like to achieve is to join both views on "mp", "groupname", "quantity" and "sumrule" (4 conditions) keeping all rows from "resultmatrix" and containing either "tracesum.value" if a corresponding row exists in "tracesum", or NULL if no corresponding row exists there.

I hope I could make my problem clear. Is there a way to create this kind of result table?

Thanks, Thomas

(2) By Stephan Beal (stephan) on 2020-10-09 17:20:34 in reply to 1 [link] [source]

I hope I could make my problem clear.

An actual SQL schema and some sample data would make it about 1000x clearer.

"Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowcharts; they'll be obvious." -- Fred Brooks.

(3.1) By Gerry Snyder (GSnyder) on 2020-10-09 20:13:54 edited from 3.0 in reply to 1 [link] [source]

If I understand your problem correctly, using a:

LEFT OUTER JOIN

is what you need to do. It returns all the rows from the left-hand table, with matching data from the right-hand table if there is any, and NULL if there is not.