From: Kim G. Pedersen (kimmacaos.com)
Date: Thu Nov 13 2003 - 14:22:56 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Thanks to You Brent and Bill Easton

and Sorry

Hrmm , that what happen when not clipping direct into mail program ,
here is the Original :-) :

Select A.DepartmentName,A.Address,P.Postcode,P.cityname,CP.firstname
from caddress A,cpostinfo P
 left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo
CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and
CACP.AddressID=A.ID and
 CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and
CCI.ContactInfoTypeID=1
where A.ID=10 and A.PostInfoID=P.ID

Something was wrong in this query above , so I did rewrite the query :

Select A.DepartmentName,A.Address,P.Postcode,P.cityname
,CP.firstname
 from caddress A,cpostinfo P

Left join CContactPerson CP
  on CP.ID=1001
left join caddresscontactperson CACP ,ccontactinfo CCI
  on CACP.ContactpersonID=1001 and CACP.AddressID=10 and
      CCI.AddressID=-1 and CCI.AddressContactpersonID=CACP.ID and
      CCI.contactInfotypeID=1

where A.ID=10 and A.PostInfoID=P.ID

but I still do not understand :

if I just have one record in caddresscontactperson the query return
a the correct answer but I caddresscontactperson are a empty table
it give my empty answer - why not

 -->company , streeet ,2000,copenhagen , NULL

I know I use addresscontactperson in my left join , but why does that
influent other that giving me a NULL for the CP.firstname value

> Your syntax is wrong, or at least not standard, if you are trying to do
> multiple left joins.
> Your ordering is not your typical for a query.
> And you say "there" instead of "where".
>
> Your query should be structure like this:
>
> SELECT <fields>
> FROM <table>, <table>,...
> LEFT JOIN <table> ON <join condition>
> LEFT JOIN <table> ON <join condition>
> LEFT JOIN <table> ON <join condition>
> ...
> WHERE <filter condition>
>
> I'm surprised your query worked at all.
>
> On Wednesday, November 12, 2003, at 05:20 PM, Kim G. Pedersen wrote:
>
>> the little Query1 works until I add the left join :
>>
>> Query 1 )
>> Select A.DepartmentName,A.Address,P.Postcode,P.cityname
>> from caddress A,cpostinfo P
>> there A.ID=10 and A.PostInfoID=P.ID
>>
>>
>> Query 2)
>> Select A.DepartmentName,A.Address,P.Postcode,P.cityname,
>> CP.firstname
>>
>> left Join CContactPerson CP , caddresscontactperson CACP
>> ,ccontactinfo
>> CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and
>> CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and
>> CCI.AddressID=-1 and CCI.ContactInfoTypeID=1
>>
>> from caddress A,cpostinfo P
>> there A.ID=10 and A.PostInfoID=P.ID
>>
>>
>> Query1 return :
>> -> company , streeet ,2000,copenhagen
>>
>>
>> Query2 return :
>> -> Empty
>>
>> I know that the jeft join will give zero result ,
>> since the table caddresscontactperson are empty.
>>
>> But I do not Understand Why I not get
>> -> company , streeet ,2000,copenhagen , NULL
>>
>> Normally a left join should not effect ur result.
>>
> --
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
>

mvh
Kim G. Pedersen
macaos/elprint Development
+45 35373808

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]