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.

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

Reply via email to