Re: left join

Hi K. I agree.
It shouldn't work if you don't group on all the non-aggregate fields in the
select clause.

On Mon, Apr 4, 2011 at 9:43 AM, Kerneels Roos <kerneels@xxxxxxxxx> wrote:

>  Hi,
>
> Another potential issue with the query is that you 'GROUP BY' on only the
> ClientID while the only aggregate function is on the DateSigned
> (max(DateSigned)).
>
> Shouldn't the group by clause look like this:
>
>
>  select C.clientid, C.name, max(datesigned) from clients C left join
>  contracts CT on C.clientid = CT.clientid and datesigned <
>  "2004-02-29"
> group by CT.clientid, c.name;
>
> If it's not required, why not?
>
> Kerneels
>
>
> On 4/4/2011 8:47 AM, black ares wrote:
> > you are right. According to the database theory where applies before
> > joins, but in practice, in all dbms where applies after joins. This
> > is because of the easy way to do it. Aplying joins before where means
> > simply evaluating the sql construct from left to right.
> >
> >
> > ----- Original Message ----- *From:* Jacques Bosch
> > <mailto:jfbosch@xxxxxxxxx> <jfbosch@xxxxxxxxx> *To:*
> programmingblind@xxxxxxxxxxxxx
> > <mailto:programmingblind@xxxxxxxxxxxxx> 
> > <programmingblind@xxxxxxxxxxxxx>*Sent:* Saturday, April 02,
>
> > 2011 5:05 PM *Subject:* Re: left join
> >
> > The problem is probably with your where clause. You are filtering the
> > final result set instead of the right hand result set. Try adding
> > your date condition as part of the left join rather than in the where
> > clause, as below.
> >
> > select C.clientid, C.name, max(datesigned) from clients C left join
> > contracts CT on C.clientid = CT.clientid and datesigned <
> > "2004-02-29" group by CT.clientid;
> >
> > Jacques
> >
> >
> > On Sat, Apr 2, 2011 at 3:00 AM, Jeff Berwick
> > <mailinglists@xxxxxxxxxxxx 
> > <mailto:mailinglists@xxxxxxxxxxxx><mailinglists@xxxxxxxxxxxx>
> >
>
> > wrote:
> >
> > Hi listers,
> >
> > My understand of the left join keyword in mysql is that it will
> > return all the items on the left ide regarding to whether they have a
> > corresponding entry on the right. I have two tables:
> >
> > clients (containing a list of clients) contracts (containing a list
> > of contracs with those clients)
> >
> > I am using:
> >
> > select C.clientid, C.name, max(datesigned) from clients C left join
> > contracts CT on C.clientid = CT.clientid where datesigned <
> > 2004-02-29" group by CT.clientid;
> >
> > I should get all 11 clients returned with null values in the
> > datesigned field. However, I only get three.
> >
> > Can anybody provide me with some understanding?
> >
> > Thx, Jeff
> >
> > __________ View the list's information and change your settings at
> > http://www.freelists.org/list/programmingblind
> >
> >
> >
> >
> > --
> >
> > Jacques Bosch
> >
> > Software Architecture and Development Independent Contractor Cell:
> > +27 824711807 Fax: +27 86 504 4726 E-Mail: jfbosch@xxxxxxxxx
>  > <mailto:jfbosch@xxxxxxxxx> <jfbosch@xxxxxxxxx>
> >
>
> --
> Kerneels Roos
> Cell: +27 (0)82 309 1998
> Skype: cornelis.roos
>
> "There are only two kinds of programming languages in the world; those
> everyone complains about, and those nobody uses."
>
>


-- 

Jacques Bosch

Software Architecture and Development
Independent Contractor
Cell: +27 824711807 Fax: +27 86 504 4726
E-Mail: jfbosch@xxxxxxxxx

Other related posts: