CODEWITHSUNDEEP
phpmysqlsqljoin
Martin Hunt
Martin Hunt

Reputation: 1155

Mysql Join without replacing value of original id

I am trying to join my users table with another table using the following query...

SELECT * FROM (`activities`)
JOIN `users` ON `users`.`id` = `activities`.`user` 
WHERE `user_subdomain` = 'hi' OR user_subdomain = '' 
ORDER BY `activities`.`id` desc 
LIMIT 10

Is there any way to do the join so that the id of the user does not replace the id of the activity?

For example, currently if there is an activity with the id of 10 and the user 2 the id will be replaced by the id of the users table and show as 2 after I run the query.

Thanks a lot for the help!

Upvotes: 1

Views: 418

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

Whenever you are joining tables, you ought to be explicit about the columns you select rather than using SELECT *, and specify column aliases for them when the same column name is used in multiple tables.

SELECT 
  activities.id,
  activities.othercol,
  /* Alias to userid */
  users.id AS userid,
  users.name,
  users.anothercolumn
FROM (`activities`)
  JOIN `users` ON `users`.`id` = `activities`.`user` 
WHERE `user_subdomain` = 'hi' OR user_subdomain = '' 
ORDER BY `activities`.`id` desc 
LIMIT 10

Though it isn't strictly necessary to prepend the table name to each, unless the column names are the same.

SELECT 
  activities.id AS activityid,
  othercol,
  users.id AS userid,
  name,
  anothercolumn

Upvotes: 5

Related Questions