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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]