SQLite Forum

Missing records from the IN list
Login

Missing records from the IN list

(1) By jose isaias cabrera (jicman) on 2021-03-16 18:30:15

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 [link]

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é