Re: [sqlite] Why do I only get one record?

2020-02-19 Thread Jose Isaias Cabrera


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?

2020-02-19 Thread Igor Korot
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?

2020-02-19 Thread Jose Isaias Cabrera

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?

2020-02-19 Thread 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  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