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?
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
