SQLite Forum

Missing records from the IN list
Login
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é