Hi, On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera <jic...@outlook.com> wrote: > > > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working now. > Thanks. > > > ________________________________ > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf > of Keith Medcalf <kmedc...@dessus.com> > Sent: Wednesday, February 19, 2020 03:09 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: Re: [sqlite] Why do I only get one record? > > > 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.
I hate MS-ism. ;-) Why not just use a simple WHERE a.id = b.id? Less typing anyway... Thank you. > > 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 > _______________________________________________ > 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
