[sqlite] Why do I only get one record?

[Jose Isaias Cabrera]

Greetings!

Newbie here again... ;-)

Please take a look at this query,

sqlite> SELECT
   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
   ...> 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 
ProjID = b.ProjID)
   ...> AND
   ...> a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...> WHERE a.ProjID IN
   ...> (
   ...> 'PR0000018284',
   ...> 'PR0000015544'
   ...> )
   ...> ORDER BY a.ProjID;
PR0000015544|2019-01-01|2020-01-01||||||
sqlite>

Why do I only get one row?  Because if I do this other query,

sqlite> SELECT
   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
   ...> 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 
ProjID = b.ProjID)
   ...> AND
   ...> a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...> WHERE a.ProjID IN
   ...> (
   ...> 'PR0000018284'
   ...> )
   ...> ORDER BY a.ProjID;
PR0000018284|2020-01-01|2020-03-01||||||
sqlite>

That project exists.  And if I do this other query,
sqlite> SELECT
   ...> a.ProjID, a.Start_date, a.Finish_Date, a.Target_Go_Live_Date,
   ...> a.BL_Start, a.BL_Finish, sum(b.AnnualDossier) as Dossier,
   ...> sum(b.Actuals) as ABTActual, sum(b.EAC) as ABTEAC
   ...> 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 
ProjID = b.ProjID)
   ...> WHERE
   ...> a.InsertDate =
   ...> (SELECT MAX(InsertDate) FROM Project_List WHERE ProjID = a.ProjID)
   ...> AND a.ProjID = b.ProjID
   ...> ;
PR0000013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01-31|76605061.443927|76125541.48|149733051.578888
sqlite>

I still only get one.  It looks like I am setting a limit, but that is not 
true.  Any help would be greatly appreciated.  Thanks.

josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users