CODEWITHSUNDEEP
mysqlduplicates
Ilya Gazman
Ilya Gazman

Reputation: 32261

MySql - Select user name from two different tables in one query

I have 3 tables

I want to select the name of the user with the most likes, and the name of the users with the most score. But my problem is that when I am writing the query I have duplicated name field there. What should I do?

Upvotes: 0

Views: 174

Answers (4)

Fathah Rehman P
Fathah Rehman P

Reputation: 8751

i use following three tables in my query

  1. usertable - contain username and userid
  2. table2 - contain userid and score
  3. table3 - contain userid and numOfLikes

To get details of user with max score use following

select usrtbl.username,usrtbl.userid,tbl2.score from  usertable usrtbl inner join table2 tbl2 on usrtbl.userid=tbl2.userid order by tbl2.score
desc limit 1

To get details of user with max number of likes use following

select usrtbl.username,usrtbl.userid,tbl3.numOfLikes from  usertable usrtbl inner join table3 tbl3 on usrtbl.userid=tbl3.userid order by tbl3.numOfLikes
desc limit 1

To get all in one query use following query

select usrtbl.username as 'user_with_max_score',usrtbl.userid as 'userid_of_user_with_max_score',
tbl2.score as 'max_score',usrtbl2.username as 'user_with_max_likes',usrtbl2.userid as 'userid_of_user_with_max_score' ,
tbl3.numOfLikes as 'max_likes' from  usertable usrtbl2 inner join table3 tbl3 inner join  usertable usrtbl inner join table2 tbl2 
on usrtbl.userid=tbl2.userid  and usrtbl2.userid=tbl3.userid order by tbl2.score desc,tbl3.numOfLikes desc
 limit 1

Upvotes: 1

snurre
snurre

Reputation: 3105

You are probably using SELECT *. You should specify the field names:

SELECT u.user_id, u.name, li.numOfLikes FROM users u INNER JOIN likes li ON (u.user_id=li.user_id) ORDER BY li.numOfLikes

SELECT u.user_id, u.name, s.score FROM users INNER JOIN scores s ON (u.user_id=s.user_id) ORDER BY s.score

Upvotes: 1

Reinstar
Reinstar

Reputation: 146

using alias may help you

SELECT a.id as id_a,
  b.id as id_b,
  c.id as id_c
from a
inner join b
  on a.id = b.a_id
inner join c
  on a.id = c.a_id

Upvotes: 2

Ricardo Ortega Magaña
Ricardo Ortega Magaña

Reputation: 2442

You can select with tablename.name

Upvotes: 0

Related Questions