You are taking the INNER JOIN with caddrescontactperson.
Presumably, you need something like this:
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
on CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID
left join ccontactinfo CCI
on CACP.ID=CCI.AddressContactPersonID
and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1
where A.ID=10 and A.PostInfoID=P.ID
> Date: Wed, 12 Nov 2003 23:20:10 +0100 (CET)
> Subject: Sql - Problem with Left Join
> From: "Kim G. Pedersen" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Hello
> 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.
> Hope Someone can give me clue
> using Mysql 4.1.0
> regards
> Kim G. Pedersen
> macaos/elprint Development
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
