From: Peter Brawley
Date: May 22 2006 8:43pm
Subject: Re: LEFT JOIN Multiple Tables
List-Archive: http://lists.mysql.com/mysql/198188
Message-Id: <4472225F.1020605@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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


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