Search code examples
mysqlinner-join

Why does this sql #1066 - Not unique table/alias: 'categories'?

SELECT
    categories.id, categories.name
AS
    parentName
FROM
    categories
INNER JOIN
    categories
ON
    categories.parent = categories.id
ORDER BY
    id
DESC

I want to inner join two columns in same table (categories).

Solution

  • That's 'cause you are joining on the same table, which needs a table alias in order to avoid confusion, as illustrated below

    FROM
    categories
INNER JOIN
    categories

    Change it to below (here c1, c2 are table aliases)

    FROM
    categories c1
INNER JOIN
    categories c2 
ON
    c1.parent = c2.id

    and adjust the SELECT clause accordingly