Re: [sqlite] Why do I only get one record?
Igor Korot, on Wednesday, February 19, 2020 04:30 PM, wrote... > > Hi, > > On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera > wrote: > > > > > > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working > now. Thanks. > > > > > > > sqlite-users on behalf of > Keith Medcalf > > Sent: Wednesday, February 19, 2020 03:09 PM > > To: SQLite mailing list > > > > > > > 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? That's what I was using originally, but I was only getting one record. I was missing HAVING. > 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 On > > >Behalf Of Jose Isaias Cabrera > > >Sent: Wednesday, 19 February, 2020 12:46 > > >To: SQLite mailing list > > >Subject: [sqlite] Why do I only get one record? > > > > > > > > >Greetings! > > > > > >Newbie here again... ;-) > > > > > >Please take a look at this query, > > > > > >sqlite> SELECT ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why do I only get one record?
Hi, On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera wrote: > > > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working now. > Thanks. > > > > From: sqlite-users on behalf > of Keith Medcalf > Sent: Wednesday, February 19, 2020 03:09 PM > To: SQLite mailing list > 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 On > >Behalf Of Jose Isaias Cabrera > >Sent: Wednesday, 19 February, 2020 12:46 > >To: SQLite mailing list > >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 > > ...> ( > > ...> 'PR018284', > > ...> 'PR015544' > > ...> ) > > ...> ORDER BY a.ProjID; > >PR015544|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 > > ...> ( > > ...> 'PR018284' > > ...> ) > > ...> ORDER BY a.ProjID; > >PR018284|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 > > ...> ; > >PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01- > >31|76605061.443927|76125541.48|149733051.57 > >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
Re: [sqlite] Why do I only get one record?
Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working now. Thanks. From: sqlite-users on behalf of Keith Medcalf Sent: Wednesday, February 19, 2020 03:09 PM To: SQLite mailing list 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. 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 On >Behalf Of Jose Isaias Cabrera >Sent: Wednesday, 19 February, 2020 12:46 >To: SQLite mailing list >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 > ...> ( > ...> 'PR018284', > ...> 'PR015544' > ...> ) > ...> ORDER BY a.ProjID; >PR015544|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 > ...> ( > ...> 'PR018284' > ...> ) > ...> ORDER BY a.ProjID; >PR018284|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 > ...> ; >PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01- >31|76605061.443927|76125541.48|149733051.57 >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
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. 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 On >Behalf Of Jose Isaias Cabrera >Sent: Wednesday, 19 February, 2020 12:46 >To: SQLite mailing list >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 > ...> ( > ...> 'PR018284', > ...> 'PR015544' > ...> ) > ...> ORDER BY a.ProjID; >PR015544|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 > ...> ( > ...> 'PR018284' > ...> ) > ...> ORDER BY a.ProjID; >PR018284|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 > ...> ; >PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01- >31|76605061.443927|76125541.48|149733051.57 >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] 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 ...> ( ...> 'PR018284', ...> 'PR015544' ...> ) ...> ORDER BY a.ProjID; PR015544|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 ...> ( ...> 'PR018284' ...> ) ...> ORDER BY a.ProjID; PR018284|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 ...> ; PR013109|2017-11-13|2019-04-30|2018-10-14|2017-11-13|2019-01-31|76605061.443927|76125541.48|149733051.57 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
