SQLite Forum

Wow! How do you come up with these SQL combinations?  Someday I will be like you. :-)

I did made these changes to the final select to make it work,

```
     )                                       -- took the comma out
   select a.*,                               -- replace Proj_List with a
          sum(b.AnnualDossier) as Dossier    -- added a b. in the sum function
     from Proj_List as a
left join Proj_Budget as b
       on a.ProjID == b.ProjID
 group by a.ProjID
 order by a.Project_Manager
;
```

and it worked. But it looks like they both have the same speed. This is the expert result for the last one you provided:

```

(no new indexes)

MATERIALIZE Proj_Budget
SEARCH o USING COVERING INDEX sqlite_autoindex_ABT_Budget_1 (ProjID=?)
LIST SUBQUERY 29
MATERIALIZE ProjIDs
SCAN 25 CONSTANT ROWS
SCAN ProjIDs
CORRELATED SCALAR SUBQUERY 30
SEARCH ABT_Budget USING COVERING INDEX ABT_Budget_idx_9bd20c76 (ProjID=?)
SEARCH o USING INDEX PL_ProjID (ProjID=?)
LIST SUBQUERY 26
SCAN ProjIDs
CORRELATED SCALAR SUBQUERY 27
SEARCH Project_List USING COVERING INDEX Project_List_idx_292415b1 (ProjID=?)
SEARCH b USING AUTOMATIC COVERING INDEX (ProjID=?)
USE TEMP B-TREE FOR ORDER BY
```

and this is for the previous one with your suggested fix:

```
(no new indexes)

SEARCH a USING INDEX PL_ProjID (ProjID=?)
LIST SUBQUERY 27
CO-ROUTINE ProjIDs
SCAN 25 CONSTANT ROWS
SCAN ProjIDs
CORRELATED SCALAR SUBQUERY 28
SEARCH Project_List USING COVERING INDEX Project_List_idx_292415b1 (ProjID=?)
SEARCH b USING COVERING INDEX PAB_ProjID_AnnualDossier_InsertDate (ProjID=?)
CORRELATED SCALAR SUBQUERY 26
SEARCH ABT_Budget USING COVERING INDEX ABT_Budget_idx_9bd20c76 (ProjID=?)
USE TEMP B-TREE FOR ORDER BY
```

Don't really understand this well, but which one is better? :-)