MySQL left join subquery fail

Question

Following query runs well in MySQL 5.x

SELECT
  m_area.id, m_area.cn_areaName, m_area.de_areaName,
  m_area.en_areaName,m_area.jp_areaName,t_shop.count
FROM
  m_area left join   
(
select t_shop.areaID, count(areaID) AS count
from t_shop
group by t_shop.areaID
) t_shop
on m_area.id = t_shop.areaID

However, when I have to run it in a 4.0.23 MySQL DB with same DB structure and data it just return following message:

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 '[

            select t_shop.areaID, count(areaID) AS count
            from t_s 

I tried many times but still failed. Is left join to subquery not allowed in MySQL 4.x ? Then that mean I have to do it with a temp table?

Thanks in advance!

Answer 1

Only thing I could think of is adding the tablename to your areaID in the subquery or renaming the reserved word count to cnt.

SELECT  m_area.id
        , m_area.cn_areaName
        , m_area.de_areaName
        , m_area.en_areaName
        ,m_area.jp_areaName
        ,t_shop.cnt
FROM     m_area 
        left join ( 
          select     t_shop.areaID
                    , count(t_shop.areaID) AS cnt 
          from       t_shop 
          group by   t_shop.areaID 
        ) t_shop on m_area.id = t_shop.areaID 

Answer 2

Subqueries were quite not well supported with MySQL 4.0 : it became possible to use them (at least, in some real, useful way) with MySQL 4.1 -- and MySQL 4.0 is really old, now...

See for instance this page of the MySQL manual : 12.2.8. Subquery Syntax (quoting, emphasis mine) :

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

With MySQL versions prior to 4.1, it was necessary to work around or avoid the use of subqueries.
In many cases, subqueries can successfully be rewritten using joins and other methods. See Section 12.2.8.11, “Rewriting Subqueries as Joins for Earlier MySQL Versions”.

Answer 3

take out ", count(areaID) AS count"

The multiple columns in the subquery is messing up the join.

A temp table should work fine ....

Have fun!