SQLite Forum

Help understanding LEFT JOIN
You have failed to account for NULL.

The conditions in the WHERE clause for tables zImport as c, zdocs as d, and t2 as e referencing the idate and id fields in each of those respectively do not permit the idate field of that table to be null.

This causes the LEFT JOIN to be a misstatement of an INNER JOIN.

However, if the LEFT JOIN is actually performed, then all the columns of the RHS table will be NULL.  And a test for equality where one of the arguments is NULL, is false.

So in your WHERE clause you need to change all the = to IS so that comparisons between nulls become TRUE rather than FALSE.


SELECT a.id, a.pid, a.yyyy, b.i, c.nn, d.dn, sum(e.amt)
FROM zMList a
    LEFT JOIN zProjs b ON a.id = b.id 
    LEFT JOIN zImport c ON a.id = c.id
    LEFT JOIN zdocs d ON a.id = d.id 
    LEFT JOIN t2 e ON a.pid = e.pid
    a.yyyy = 2020
    AND a.idate IS (SELECT MAX(idate) from zMList where id IS a.id)
    AND c.idate IS (SELECT MAX(idate) from zImport where id IS c.id)
    AND d.idate IS (SELECT MAX(idate) from zdocs where id IS d.id)
    AND e.indate IS (SELECT MAX(indate) from t2 where pid IS e.pid)
GROUP BY a.pid