CODEWITHSUNDEEP
mysqlsql
Ghias
Ghias

Reputation: 133

Query on the result of another query giving errors

I'm trying to run a query on the result set of another query but getting errors which I cannot understand. I'm sure it's something small but I'm somehow missing it. This is the query:

SELECT
  RLID,
  NAME,
  GROUP_CONCAT(SUBQUERY.Items SEPARATOR ', ') AS Items
FROM
  (
  SELECT
    `rel_menu_item`.`ID` AS `RLID`,
    `menu`.`Name` AS `Menu_Name`,
    `item`.`Name` AS `Items`
  FROM
    `rel_menu_item`
  JOIN
    `menu` ON `menu`.`ID` = `rel_menu_item`.`Menu_ID`
  JOIN
    `item` ON `item`.`ID` = `rel_menu_item`.`Item_ID`
) AS SUBQUERY
GROUP BY
  SUBQUERY.Name

Errors:

3 errors were found during analysis.

  1. An expression was expected. (near "(" at position 90)
  2. Unexpected token. (near "(" at position 90)
  3. This type of clause was previously parsed. (near "SELECT" at position 95)

Upvotes: 0

Views: 252

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You should be writing this query without a subquery at all:

SELECT m.Id as RLID, m.Name as Menu_Name
      GROUP_CONCAT(i.name SEPARATOR ', ') AS Items
FROM rel_menu_item rmi JOIN
     menu m
     ON m.ID = rmi.Menu_ID JOIN
     item i
     ON i.ID = rmi.Item_ID
GROUP BY m.Id, m.Name;

Notes:

  • The subquery is not needed.
  • Table aliases make the query easier to write and to read.
  • It is better to use the column from the primary key table menu.id rather than from the references foreign key. It doesn't really make a difference with inner joins, but it does make a difference with outer joins; hence, it is a bad practice.
  • You should include all non-aggregated columns in the GROUP BY.

Upvotes: 0

Ghias
Ghias

Reputation: 133

I found the errors. Here is the correct query:

SELECT
  SUBQUERY.RLID,
  SUBQUERY.Menu_Name,
  GROUP_CONCAT(SUBQUERY.Items SEPARATOR ', ') AS Items
FROM
  (
  SELECT
    rel_menu_item.ID AS RLID,
    menu.Name AS Menu_Name,
    item.Name AS Items
  FROM
    rel_menu_item
  JOIN
    menu ON menu.ID = rel_menu_item.Menu_ID
  JOIN
    item ON item.ID = rel_menu_item.Item_ID
) AS SUBQUERY
GROUP BY
  SUBQUERY.Menu_Name

Upvotes: 1

Related Questions