List:General Discussion« Previous MessageNext Message »
From:Scott Baker Date:May 22 2006 11:56pm
Subject:Re: LEFT JOIN Multiple Tables
View as plain text  
Perfect. This is exactly what I needed. Is there some place I get
some more documentation on the specifics of the ISO-compatible
queries? Might save me some hair-pulling-out in the future.

Scott

Peter Brawley wrote:
> Scott
> 
>>SELECT *
>>FROM UserInfo u, DslInfo d
>>LEFT JOIN DslExtra e ON d.DslID = e.DslID
>>LEFT JOIN ExtraAddr a ON a.UserID = u.UserID
>>WHERE u.UserID = d.UserID;
> 
>>However it appears this syntax is not valid in MySQL 5.x
> 
> Right, as the 5.x docs say, 5.x wants ISO-compatible explicit joins, ie
> 
> SELECT *
> FROM UserInfo u
> INNER JOIN DslInfo d USING (UserID)
> LEFT JOIN DslExtra e ON d.DslID = e.DslID
> LEFT JOIN ExtraAddr a ON a.UserID = u.UserID;
> 
> PB
> 
> -----
> 
> Scott Baker wrote:
>> I have four tables that I join to get one record set about a
>> customer. They are: UserInfo, ExtraAddr, DslInfo, and DslExtra.
>>
>> Specifically the main tables are joined with:
>>
>> SELECT * FROM UserInfo u, DslInfo d WHERE u.UserID = d.UserID;
>>
>> I've been adding more data to other tables and LEFT JOINING to get
>> the data (since it's optional).
>>
>> SELECT *
>> FROM UserInfo u, DslInfo d
>> LEFT JOIN DslExtra e ON d.DslID = e.DslID
>> LEFT JOIN ExtraAddr a ON a.UserID = u.UserID
>> WHERE u.UserID = d.UserID;
>>
>> However it appears this syntax is not valid in MySQL 5.x (It works
>> on 4.x). I need to LEFT JOIN *two* tables, but I can't seem to get
>> it. I just LEFT JOIN the DslExtra table in the above example it
>> works just fine, it's only when I try and do the second that I get
>> an error.
>>
>> Unknown column 'u.UserID' in 'on clause'
>>
>> Can I not do this type of multi table left join with 5.x? Or do I
>> need to recraft the query?
>>
>>   
> 
> 

-- 
Scott Baker - RHCE
Canby Telcom System Administrator
503.266.8253
Thread
LEFT JOIN Multiple TablesScott Baker22 May
  • Re: LEFT JOIN Multiple TablesPeter Brawley22 May
    • Re: LEFT JOIN Multiple TablesScott Baker23 May
      • Re: LEFT JOIN Multiple TablesPeter Brawley23 May