MySQL Not unique table/alias

Question

I looked at the answers of others having the same problem, but I can't figure out how to fix the "Not unique table/alias".

SELECT m.*, u.*
  FROM ".TABLE_PREFIX."users_medals u
  LEFT JOIN ".TABLE_PREFIX."medals m ON u.medal_id = m.medal_id
  WHERE u.user_id IN (".$post['uid'].")
  AND m.medal_level = (
    SELECT MAX(".TABLE_PREFIX."medals.medal_level) FROM ".TABLE_PREFIX."medals
    LEFT JOIN ".TABLE_PREFIX."medals ON ".TABLE_PREFIX."users_medals.medal_id = ".TABLE_PREFIX."medals.medal_id
    WHERE ".TABLE_PREFIX."users_medals.user_id = u.user_id
    AND ".TABLE_PREFIX."medals.medal_type = m.medal_type
  )

TABLE_PREFIX is what my script uses to define the database table prefix.

possible duplicate of [MySQL & PHP - Not unique table/alias](http://stackoverflow.com/questions/2077355/mysql-php-not-unique-table-alias) — Phil, Aug 18 '11 at 01:53
Where exactly are you looking for the non-unique table/alias? — nderjung, Aug 18 '11 at 01:54

score 1 · Accepted Answer · answered Aug 18 '11 at 01:53

1

In your sub-query

LEFT JOIN ".TABLE_PREFIX."medals

I think that should be

LEFT JOIN ".TABLE_PREFIX."users_medals

answered Aug 18 '11 at 01:53

Phil

157,677
23
242
245

score 0 · Answer 2 · answered Aug 18 '11 at 02:02

In the subquery you have two medals tables without aliases. I think the FROM ".TABLE_PREFIX."medals should be FROM ".TABLE_PREFIX."users_medals:

SELECT m.*, u.*
  FROM ".TABLE_PREFIX."users_medals u
  LEFT JOIN ".TABLE_PREFIX."medals m ON u.medal_id = m.medal_id
  WHERE u.user_id IN (".$post['uid'].")
  AND m.medal_level = (
    SELECT MAX(".TABLE_PREFIX."medals.medal_level) FROM ".TABLE_PREFIX."users_medals
    LEFT JOIN ".TABLE_PREFIX."medals ON ".TABLE_PREFIX."users_medals.medal_id = ".TABLE_PREFIX."medals.medal_id
    WHERE ".TABLE_PREFIX."users_medals.user_id = u.user_id
    AND ".TABLE_PREFIX."medals.medal_type = m.medal_type
  )

MySQL Not unique table/alias

2 Answers2