Problem with subquery in MySQL

[morhen]

Hello,
I've got following query:

SELECT y.c_node_id, y.c_code_id, y.c_rule_id
FROM 
	CODE c2
	JOIN (
		SELECT c.c_id AS c_code_id, c.c_code, r.c_id AS c_rule_id, r.c_node_id, 1 AS 'priority'
		FROM
			CODE c
			JOIN HRY h ON h.c_dimension_id = c.c_dimension_id
			JOIN NODE n ON h.c_id = n.c_hierarchy 
			JOIN RULE r ON n.c_id = r.c_node_id
		WHERE h.c_id = 1 AND r.c_value = c.c_code AND r.c_rule_type = 'SR'

		UNION

		SELECT c.c_id AS c_code_id, c.c_code, r.c_id, r.c_node_id, 2 AS 'priority'
		FROM
			CODE c
			JOIN HRY h ON h.c_dimension_id = c.c_dimension_id
			JOIN NODE n ON h.c_id = n.c_hierarchy 
			JOIN RULE r ON n.c_id = r.c_node_id
		WHERE h.c_id = 1 AND (c.c_code BETWEEN r.c_value AND r.c_to_value) AND r.c_rule_type = 'RR'
		
	) AS y ON y.c_code_id = c2.c_id
WHERE y.priority = (
	SELECT MIN(priority)
	FROM (
		SELECT r.c_id, 1 AS 'priority'
		FROM
			NODE n
			JOIN RULE r ON n.c_id = r.c_node_id
		WHERE n.c_hierarchy = 1 AND r.c_value = c2.c_code AND r.c_rule_type = 'SR'
		
		UNION

		SELECT r.c_id, 2 AS 'priority'
		FROM
			NODE n
			JOIN RULE r ON n.c_id = r.c_node_id
		WHERE n.c_hierarchy = 1 AND (c2.c_code BETWEEN r.c_value AND r.c_to_value) AND r.c_rule_type = 'RR'
	) z)

In SQL Server this query executes without any problems but in MySQL it complains that column 'c2.c_code_id' in 'where clause' is unknown (line 31). What is going on? c2 is in the FROM clause of the outer query.

Thanks in advance.

morhen

[coolsti]

WHERE n.c_hierarchy = 1 AND r.c_value = c2.c_code AND r.c_rule_type = 'SR'

UNION

SELECT r.c_id, 2 AS 'priority'
FROM
NODE n
JOIN RULE r ON n.c_id = r.c_node_id
WHERE n.c_hierarchy = 1 AND (c2.c_code BETWEEN r.c_value AND r.c_to_value) AND r.c_rule_type = 'RR'
) z)
[/CODE]

In SQL Server this query executes without any problems but in MySQL it complains that column 'c2.c_code_id' in 'where clause' is unknown (line 31). What is going on? c2 is in the FROM clause of the outer query.

Thanks in advance.

morhen

You are saying that the error is with column c2.c_code_id but in your line 31 I only see c2.c_code . But maybe this is not a mistake in your query, but only an error in the description of your problem?

Mysql handles things differently than MSSQL. What version of Mysql are you using?

Have you tried to replace c2.c_code with CODE.c_code ? Do you still get the error? In other words, why not refer to the table CODE directly here, instead of using the alias "c2" which you have given to it in the outer query?