Why do I only get one record?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Why do I only get one record?

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do I only get one record?

Keith Medcalf

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 <[hidden email]> On
>Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, 19 February, 2020 12:46
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do I only get one record?

Jose Isaias Cabrera-4

Thanks, Keith.  Darn it!  GROUP BY and ORDER BY!  Got it, it's working now.  Thanks.


________________________________
From: sqlite-users <[hidden email]> on behalf of Keith Medcalf <[hidden email]>
Sent: Wednesday, February 19, 2020 03:09 PM
To: SQLite mailing list <[hidden email]>
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 <[hidden email]> On
>Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, 19 February, 2020 12:46
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do I only get one record?

Igor Korot
Hi,

On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera <[hidden email]> wrote:

>
>
> Thanks, Keith.  Darn it!  GROUP BY and ORDER BY!  Got it, it's working now.  Thanks.
>
>
> ________________________________
> From: sqlite-users <[hidden email]> on behalf of Keith Medcalf <[hidden email]>
> Sent: Wednesday, February 19, 2020 03:09 PM
> To: SQLite mailing list <[hidden email]>
> 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 <[hidden email]> On
> >Behalf Of Jose Isaias Cabrera
> >Sent: Wednesday, 19 February, 2020 12:46
> >To: SQLite mailing list <[hidden email]>
> >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
> >[hidden email]
> >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why do I only get one record?

Jose Isaias Cabrera-4


Igor Korot, on Wednesday, February 19, 2020 04:30 PM, wrote...

>
> Hi,
>
> On Wed, Feb 19, 2020 at 2:12 PM Jose Isaias Cabrera <[hidden email]>
> wrote:
> >
> >
> > Thanks, Keith. Darn it! GROUP BY and ORDER BY! Got it, it's working
> now. Thanks.
> >
> >
> > ________________________________
> sqlite-users <[hidden email]> on behalf of
> Keith Medcalf <[hidden email]>
> > Sent: Wednesday, February 19, 2020 03:09 PM
> > To: SQLite mailing list <[hidden email]>
> > >
> >
> > 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 <[hidden email]> On
> > >Behalf Of Jose Isaias Cabrera
> > >Sent: Wednesday, 19 February, 2020 12:46
> > >To: SQLite mailing list <[hidden email]>
> > >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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users