Problem in SQL query #1066 – Not unique table/alias

I have a problem with this query:

SELECT * 
FROM code
INNER JOIN relations ON tags.id = relations.tag
INNER JOIN code ON relations.code = code.id
WHERE tags.name LIKE '%value%' 
ORDER BY code.id ASC

I get this error:

1066 – Not unique table/alias: ‘code’

Answer

The error seems pretty clear. You have mentioned code three times in the from clause. What does code refer to?

In your case, I think the solution is to remove all the references. The first should be the tags table — I think. The last doesn’t seem useful. So:

SELECT *
FROM tags t JOIN
     relations r
     ON t.id = r.tag JOIN
     code cr
     ON r.code = cr.id 
WHERE t.name LIKE '%value%'
ORDER BY t.id ASC;

Note that I introduced table aliases. If you do need a second reference to code, you can just give it a new alias.