CODEWITHSUNDEEP
mysqlsqljoinforeign-keys
Aboodnet
Aboodnet

Reputation: 143

How to join multiple foreign keys that points to single primary key?

Dears,

I have two separate sql statements below. Independently they work fine. 

select teams.team_name_eng AS "TNAME1"
from matches, teams
where matches.team1_uid = teams.tid;

select teams.team_name_eng AS "TNAME2"
from matches, teams
where matches.team2_uid = teams.tid

How can i combine those statements into one statement? I want the output to be

TNAM1     TNAME2
-----     ------
playerA   PlayerB
playerC   playerD

Note: foreign-key relationships already created.

Thanks,

Upvotes: 1

Views: 1205

Answers (7)

Rupesh Singra
Rupesh Singra

Reputation: 71

Here is the query you might be looking for.

select teams.team_name_eng AS "TNAME1", teams.team_name_eng AS "TNAME2"
from matches, teams as teams_1, teams as teams_2 where matches.team1_uid = teams_1.tid
AND matches.team2_uid = teams_2.tid ;

Upvotes: 1

Biplab Sarker
Biplab Sarker

Reputation: 13

Please check this query

Select t.team_name_eng as"TNAME1", t2.team_name_eng as "TNAME2" from matches m  join teams t on m.team1_uid = t.tid join teams t2 on m.team2_uid = t2.tid

Upvotes: 1

Guy Louzon
Guy Louzon

Reputation: 1203

basic join join the same table twice, each time with a different key:

select t.team_name_eng AS "TNAME1",
t2.team_name_eng AS "TNAME2"
from matches m
LEFT JOIN teams t ON m.team1_uid = t.tid
LEFT JOIN teams t2 ON m.team2_uid = t.tid
;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

use teams table in join 2 times , 1 for team1_uid and another for team2_uid

  select t.team_name_eng AS TNAME1,t1.team_name_eng as TNAME2
    from  matches m join  teams t
    on  m.team1_uid = t.tid
    join teams t1
        and m.team2_uid = t1.tid

Upvotes: 1

Siva
Siva

Reputation: 1519

Use Join to get the answer

select teams1.team_name_eng AS "TNAME1",teams2.team_name_eng AS "TNAME2"
from matches
join teams as teams1 on teams1.tid = matches.team1_uid
join teams as teams2 on teams2.tid = matches.team2_uid

Upvotes: 2

Fahmi
Fahmi

Reputation: 37473

Use join twice with different alias

select t.team_name_eng AS "TNAME1", t1.team_name_eng AS "TNAME2"
from matches inner join teams t on matches.team1_uid = t.tid
inner join teams t1 on matches.team2_uid = t1.tid

Upvotes: 1

Francois
Francois

Reputation: 524

You have to use alias for your table :

select teams_1.team_name_eng AS "TNAME1", teams_2.team_name_eng AS "TNAME2"
from matches, teams as teams_1, teams as teams_2
where matches.team1_uid = teams_1.tid
AND matches.team2_uid = teams_2.tid ;

Upvotes: 1

Related Questions