Error with inner join mysql query

Question

Please help with inner join error. I was done this query in MS ACCESS - it's work, but when it in phpmyadmin or cmd - error

SELECT zakaz.c_id, Count(zakaz.c_id) AS [counter] 
   FROM country JOIN ((resort JOIN hotel ON resort.res_id = hotel.res_id) 
   JOIN ([number] JOIN zakaz ON (number.[num_id] = zakaz.[num_id]) 
   AND   (number.[num_id] = zakaz.[cost])) ON hotel.h_id = number.[h_id]) 
   ON country.c_id = resort.c_id GROUP BY zakaz.c_id;

Answer 1

In MySQL, the square brackets ([ ]) are not valid characters for an identifier.

In Access, those are used to enclose an identifier. In MySQL, we use backtick characters to enclose an identifier that needs to be escaped. (This allows us to include spaces or other characters which are not normally allowed in an identifier, and allows us to use reserved words as identifiers. In your query, none of the identifiers need to escaped in backticks.

Also, in MySQL, those parens aren't necessary.

The query could be expressed in a form that is easier for the human reader to decipher. For example:

  SELECT zakaz.c_id
       , COUNT(zakaz.c_id) AS `counter`
    FROM country
    JOIN resort
      ON resort.c_id = country.c_id
    JOIN hotel
      ON hotel.res_id = resort.res_id
    JOIN number
      ON number.h_id = hotel.h_id
    JOIN zakaz
      ON zakaz.num_id = number.num_id 
     AND zakaz.cost   = number.num_id 
   GROUP BY zakaz.c_id

(It's bit of an odd condition that zakaz.cost needs to be equal to number.num_id, as well as zakaz.num_id... that appears to be the condition specified in the original query.)