1) In the first two query's, why do you have a condition on the LHS table in the LEFT JOIN conditions? 2) In the last query, why do you have a condition on the RHS table of the LEFT JOIN in the WHERE clause?
These would seem to indicate that you are using a LEFT JOIN when you really do not want a LEFT JOIN (maybe you are a LEFT lover?) ... it is a common ailment. Also, you are only getting one row because you only asked for one row. If you request an aggregate and specify no GROUP BY then you can only ever get one row as a result -- the one aggregate row. If you want more than one row you need to specify by what you want the results grouped in the GROUP BY clause. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of Jose Isaias Cabrera >Sent: Wednesday, 19 February, 2020 12:46 >To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> >Subject: [sqlite] Why do I only get one record? > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
