As an SQL newbie, I still cannot figure out, what is wrong in the syntax below:

SELECT wd.id, wdp.age, wd.coins_needed, wd.power
FROM Wands AS wd
    INNER JOIN Wands_Property AS wdp ON wd.code = wdp.code WHERE wdp.is_evil = 0
    INNER JOIN (SELECT w.id, wp.age, MIN(w.coins_needed), w.power 
                FROM Wands as w
                JOIN Wands_Property as wp 
                ON w.code = wp.code
                GROUP BY wp.age, w.power) AS min_wd ON min_wd.id = wd.id
ORDER BY  wd.power DESC, wdp.age DESC

Tables:

Wands: id (Integer), code (Integer), coins_needed (Integer), power (Integer)

Wands_Property: code (Integer), age (Integer), is_evil (Integer)

I get the following error:

ERROR 1064 (42000) at line 1: 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 'INNER JOIN (SELECT w.id, wp.age, MIN(w.coins_needed), w.power F' at line 4

I presume, there is something wrong with the syntax, but the MySql compiler is not really helpful :)

CodePudding user response：

Just some minor syntax problems:

1. The WHERE clause was misplaced. It needs to follow all joins.
2. The field w.id was mentioned in the GROUP BY clause of the sub-select but was not in the fields selected by the subquery.

The updated statement becomes:

SELECT wd.id, wdp.age, wd.coins_needed, wd.power
  FROM Wands AS wd
  INNER JOIN Wands_Property AS wdp
    ON wd.code = wdp.code 
  INNER JOIN (SELECT w.id, wp.age, MIN(w.coins_needed), w.power 
                FROM Wands as w
                JOIN Wands_Property as wp 
                  ON w.code = wp.code
                GROUP BY w.id, wp.age, w.power) AS min_wd
    ON min_wd.id = wd.id
  WHERE wdp.is_evil = 0
  ORDER BY  wd.power DESC, wdp.age DESC

db<>fiddle here