Re: left join
- From: "black ares" <matematicianu2003@xxxxxxxxxxx>
- To: <programmingblind@xxxxxxxxxxxxx>
- Date: Mon, 4 Apr 2011 09:47:13 +0300
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
To: 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>
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
//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
Other related posts:
