SQLite Forum

joining tables on multiple conditions; how to do it when second table lacks some rows?
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 

  s.value AS mp, 
  g.groupname AS groupname, 
  q.name AS quantity, 
  q.sumrule AS sumrule
  generate_series(1,403) AS s, 
  (SELECT DISTINCT groupname FROM "group") g, 
  (SELECT * FROM quantity WHERE unit='1' GROUP BY name, unit, sumrule) q
  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?