Missing records from the IN list
(1) By jose isaias cabrera (jicman) on 2021-03-16 18:30:15 [link]
Greetings. The following query is missing 3 records. I know that it's probably because the LEFT JOIN does not have those records, but I have added a COALESCE to arrange for those missing records. Any ideas how to get this to work? ``` SELECT a.ProjID, COALESCE(sum(b.AnnualDossier), 0.00) AS Dossier FROM Project_List AS a LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID AND ( b.InsertDate = ( SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID ) OR b.InsertDate IS Null ) WHERE a.ProjID IN ( SELECT ProjID FROM Project_List WHERE 1=1 AND ( a.ProjID IN ('PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187') ) AND InsertDate = ( SELECT MAX(InsertDate) FROM Project_List WHERE 1=1 AND ( a.ProjID IN ('PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187') ) ) ) AND a.InsertDate = (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID) GROUP BY a.ProjID; PR0000018256|0.0 PR0000018262|0.0 sqlite> ``` Thanks for your support. josé
(2) By Keith Medcalf (kmedcalf) on 2021-03-16 19:12:35 in reply to 1 [link]
Are you sure these should be correlated and not direct references to the parent: `a.ProjID IN ('PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187')`
(3) By jose isaias cabrera (jicman) on 2021-03-16 19:25:12 in reply to 2 [link]
>Are you sure these should be correlated and not direct references to the parent: That is probably my problem. I have tried to rewrite it, but I still end up missing the 3. Let me try to rewrite it again. Back to the drawing board. :-) Thanks, Keith.
(4) By Keith Medcalf (kmedcalf) on 2021-03-16 20:02:42 in reply to 3 [link]
Also note that you can use TOTAL(...) rather than COALESE(SUM(...),0). TOTAL is the same as sum except that it always returns a double with a default value of 0.0.
(5) By jose isaias cabrera (jicman) on 2021-03-16 20:13:21 in reply to 4 [link]
>Also note that you can use TOTAL(...) rather than COALESE(SUM(...),0). > >TOTAL is the same as sum except that it always returns a double with a default value of 0.0. Thanks. Didn't know that. (So much to read and learn, and yet do my work. :-))
(6) By jose isaias cabrera (jicman) on 2021-03-17 14:21:32 in reply to 2
I was able to rewrite the query and this one works fine: ``` SELECT a.ProjID, total(b.AnnualDossier) AS Dossier FROM Project_List AS a LEFT JOIN ABT_Budget AS b ON a.ProjID = b.ProjID AND b.InsertDate = ( SELECT MAX(InsertDate) FROM ABT_Budget WHERE b.ProjID = ProjID OR b.InsertDate IS Null ) WHERE a.ProjID IN ( 'PR0000019149','PR0000018256','PR0000018262','PR0000019185','PR0000019187' ) AND a.InsertDate = ( SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID ) GROUP BY a.ProjID; PR0000018256|0.0 PR0000018262|0.0 PR0000019149|0.0 PR0000019185|0.0 PR0000019187|0.0 ``` The .expert command suggests an INDEX, which I am going to add: ``` sqlite> .expert CREATE INDEX ABT_Budget_idx_2cd87472 ON ABT_Budget(InsertDate DESC); ``` Which I am planning to add. Thanks, Keith. josé