[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