LEFT JOIN and alias syntax error

[whitedragon101]

MYSQL Version : 5.0.91

 

I have two queries which run fine however when I alias them and left join them I get a syntax error :

 

QUERY_1

SELECT * FROM(

(SELECT lga_id , COUNT(school_id_p) as num_of_schools_unapproved FROM schools WHERE EXISTS ( SELECT * FROM orders WHERE approved = 'no' AND schools.school_id_p = orders.school_id) GROUP BY lga_id) AS table1

LEFT JOIN

(SELECT lga_id_p, name FROM lga) AS table2


ON table1.lga_id = table2.lga_id_p

)

 

produces:

 

lga_id num_of_schools_unapproved lga_id_p name

2 2 2 lga_name2

4 1 4 lga_name4

5 1 5 lga_name

 

which is correct

 

QUERY_2

SELECT lga_id , COUNT(schools.school_id_p) as num_of_schools FROM schools GROUP BY lga_id

 

produces :

 

lga_id num_of_schools

1 5

2 5

3 5

4 5

5 5

6 3

7 2

 

which is correct.

 

 

However when I try to call them table3 and table 4 and do a LEFT JOIN on the lga_id I get a syntax error.  Any help much appreciated :

 

SELECT * FROM(


SELECT * FROM(

(SELECT lga_id , COUNT(school_id_p) as num_of_schools_unapproved FROM schools WHERE EXISTS ( SELECT * FROM orders WHERE approved = 'no' AND schools.school_id_p = orders.school_id) GROUP BY lga_id) AS table1

LEFT JOIN

(SELECT lga_id_p, name FROM lga) AS table2


ON table1.lga_id = table2.lga_id_p

) AS table3 LEFT JOIN


(SELECT lga_id , COUNT(schools.school_id_p) as num_of_schools FROM schools GROUP BY lga_id) AS table4

ON table3.lga_id = table4.lga_id

)

[kickstart]

Hi

 

What is the syntax error you are getting?

 

Can't see anything obvious although the code is a bit nasty to read

 

All the best

 

Keith

[whitedragon101]

 

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table3 LEFT JOIN (SELECT lga_id , COUNT(schools.school_id_p) as num_of_scho' at line 15