[thelist] Adapting MS SQL query to MySQL

Maximillian Schwanekamp anaxamaxan at neptunewebworks.com
Thu Apr 15 12:22:43 CDT 2004


How does one properly nest queries in MySQL?  This is very flexible in MS
SQL.  For example, I have three tables: members (member info), statecodes
(2-letter us/canada state/province codes), countrycodes (ISO country codes).
Say I want to get member name, state name, country name.  My query in MS SQL
might be something like this (simplified for clarity):
SELECT sq.first_name, sq.state_name, cc.country_name
FROM (
 SELECT m.first_name,sc.state_name,m.country_code
 FROM members m
 INNER JOIN statecodes sc
 ON m.state_code = sc.state_code
 WHERE m.id=1
) sq
INNER JOIN countrycodes cc
ON sq.country_code = cc.country_code

Works fine MS SQL, but not MySQL.  I know this can be done using the
"equijoin" comma operator, like this:
SELECT m.first_name,sc.state_name,cc.country_name
FROM members m, statecodes sc, countrycodes cc
WHERE m.state_code = sc.state_code AND m.country_code = cc.country_code

However not only is that deprecated SQL, but it won't work with more complex
queries, eg with subqueries on both sides of the outermost JOIN.  Is it
necessary to use temporary tables to achieve this in MySQL?

Maximillian Von Schwanekamp
NeptuneWebworks.com
voice: 541-302-1438
fax: 208-730-6504




More information about the thelist mailing list