Duplicate column name in JOIN in mysql

Question

I have a problem with this sql

SELECT COUNT( * )
FROM (

  SELECT *
  FROM `user` `t`
  JOIN `user_relation` r ON ( t.user_id = r.follower_id
  OR t.user_id = r.user_id )
  WHERE r.status = "active"
  AND (
    r.user_id =125
    OR r.follower_id =125
  )
  AND t.user_id !=125
  GROUP BY t.username
)sq

I always get the error message: "# 1060 - Duplicate the column name" user_id ". Can someone help / explain what I did wrong?

Thanks in advance Yang

Answer 1

You need to provide alias columns in the inner query

SELECT COUNT( * )
FROM (

  SELECT t.*
  FROM `user` `t`
  JOIN `user_relation` r ON ( t.user_id = r.follower_id
  OR t.user_id = r.user_id )
  WHERE r.status = "active"
  AND (
    r.user_id =125
    OR r.follower_id =125
  )
  AND t.user_id !=125
  GROUP BY t.username
)sq

Since you are interested count(*), you can return either t.*, or r.*, or any column, provided that the column names in the internal MUST query are unique, or if they are the same in both tables than the prefix with the table alias name.

Answer 2

AT Inner Query

SELECT *
  FROM `user` `t`
  JOIN `user_relation` r ON ( t.user_id = r.follower_id
  OR t.user_id = r.user_id )
  WHERE r.status = "active"
  AND (
    r.user_id =125
    OR r.follower_id =125
  )
  AND t.user_id !=125
  GROUP BY t.username

** select * ** t.user_id r.user_id .

*. .

select t.* / select r.*