How to join two tables which one has two samples of the other one (and we want to see them all in th-CodePudding

I have two tables in my SQL database, Team and Match. Each match has two team ids - one for the host_team and one for the guest_team.

My question is: how could I join these two tables to have the data of the matches, with the full data of both host_team and guest_team in one unique dataset?

Team:

id	name
1	x
2	y

Match:

id	host_id	guest_id
1	1	2

Final dataset:

match_id	host_id	host_name	guest_id	guest_name
1	1	x	2	y

I would be thankful if you help me with solving this problem :)

CodePudding user response：

Just left join Team to Match twice

SELECT 
  match.match_id
, match.host_id
, host.name AS host_name
, match.guest_id
, guest.name AS guest_name
FROM Match AS match
LEFT JOIN Team AS host ON host.id = match.host_id
LEFT JOIN Team AS guest ON guest.id = match.guest_id

CodePudding user response：

Either way you need to query the Team table twice, one way is to use an inline-select correlated on the appropriate id

select m.Id as match_id,
    m.host_id,
    (select name from Team t where t.id=m.host_id) host_name,
    m.guest_id
    (select name from Team t where t.id=m.guest_id) guest_name,
from Match m