List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 23 2006 12:48am
Subject:Re: LEFT JOIN Multiple Tables
View as plain text  
Scott,
> 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.
>   
See 'Changes in 5.0.12' on the Joins manual page 
(http://dev.mysql.com/doc/refman/5.1/en/join.html).

PB

-----
> 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?
>>>
>>>   
>>>       
>>     
>
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.7.0/345 - Release Date: 5/22/2006
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