CODEWITHSUNDEEP
mysqlsql
androniennn
androniennn

Reputation: 3137

SELECT query from 2 tables

I have a table matches that I want to get from it the users that has the best score:

SELECT userId,SUM(score) AS score FROM `matches` GROUP BY userId ORDER BY score DESC

This output 2 columns userId and score. Good. Now I have a users table, and I want to have a more detailed output of that userId. For example, I want to have: userId-firstName-lastName-phone-address-score. Is this possible with a simple sql query ? Thank you.

Upvotes: 1

Views: 56

Answers (3)

TZHX
TZHX

Reputation: 5377

Yes, simply use a JOIN statement, like:

SELECT m.userId, SUM(m.score) AS score, u.firstName, u.lastName FROM `matches` AS m
INNER JOIN `users` AS u ON u.userId = m.userId
GROUP BY userId ORDER BY score DESC

Upvotes: 2

Arion
Arion

Reputation: 31239

You could just JOIN the tables like this:

SELECT 
    matches.userId,
    SUM(matches.score) AS score,
    users.firstName,
    users.lastName,
    users.phone,
    users.address
FROM 
    `matches`
    JOIN users
        ON `matches`.userId=users.userId
GROUP BY 
    matches.userId,
    users.firstName,
    users.lastName,
    users.phone,
    users.address 
ORDER BY 
    score DESC

Reference:

Upvotes: 3

Vishal Zanzrukia
Vishal Zanzrukia

Reputation: 4973

SELECT userId, firstName, lastName, phone, address, SUM(score) AS score FROM matches join users on matches.user_id = users.user_id GROUP BY userId ORDER BY score DESC

Upvotes: 2

Related Questions