[thelist] Adapting MS SQL query to MySQL

Ken Schaefer ken at adOpenStatic.com
Thu Apr 15 20:27:58 CDT 2004


In this case, why can't you just do two INNER JOINS?

 SELECT
    m.first_name,
    sc.state_name,
    cc.country_name
FROM
    members m
INNER JOIN
    statecodes sc
ON
    m.state_code = sc.state_code
INNER JOIN
    countrycodes cc
ON
    m.country_code = cc.country_code
WHERE
    m.id = 1

Cheers
Ken


----- Original Message ----- 
From: "Maximillian Schwanekamp" <anaxamaxan at neptunewebworks.com>
To: "TheList at Evolt" <thelist at lists.evolt.org>
Sent: Friday, April 16, 2004 3:22 AM
Subject: [thelist] Adapting MS SQL query to MySQL


: 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