CODEWITHSUNDEEP
mysqlsql
socm_
socm_

Reputation: 787

How to select two joined columns?

I have a table with two columns: player1_id and player2_id - they are linked with id's in users table (primary key)

I am trying to select users name using their id's with query below but it gives me two rows instead of one

SELECT users.name, tournaments_results.* FROM tournaments_results JOIN users ON tournaments_results.p1_id = users.id
UNION
SELECT users.name, tournaments_results.* FROM tournaments_results JOIN users ON tournaments_results.p2_id = users.id

how to select users.name as two different columns as name_player_1 and name_player_2 accordingly in one row in result?

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You use two joins:

SELECT tournaments_results.*, u1.name, u2.name
FROM tournaments_results tr LEFT JOIN
     users u1
     ON tr.p1_id = u1.id LEFT JOIN
     users u2
     ON tr.p2_id = u2.id;

This uses LEFT JOIN, in case one of the columns doesn't match.

Upvotes: 2

Related Questions