How to retrieve data of two columns from two tables?

Question

I have two Table MATCH_MASTER and TEAM_MASTER

MATCH_MASTER

|---------------------------|
|M_ID | TEAM1_ID | TEAM2_ID |
|---------------------------|
|  1  |     1    |     2    |

TEAM_MASTER

|T_ID | T_NAME |
|--------------|
|  1  |    A   |
|  2  |    B   |

I want to select M_ID, team1 name and team2 name.

My query result should look like this:

|------------------------|
|M_ID | t1_Name | t2Name |
|------------------------|
|  1  |    A    |   B    |

How can I achieve this?

Answer 1

Try following.

select T1.M_ID, 
    (select T2.T_NAME from TEAM_MASTER as T2 
    where T1.TEAM1_ID = T2.T_ID) as t1_Name,
    (select T2.T_NAME from TEAM_MASTER as T2 
    where T1.TEAM2_ID = T2.T_ID) as t2Name
    from MATCH_MASTER as T1;

Answer 2

This solution (while late) accomplishes the same goal but without the sub-selects, it should perform much faster for larger data sets.

SELECT MM.M_ID
    , TM1.T_NAME AS t1_Name
    , TM2.T_NAME AS t2_Name
FROM MATCH_MASTER AS MM
    INNER JOIN TEAM_MASTER AS TM1 ON TM1.T_ID = MM.TEAM1_ID
    INNER JOIN TEAM_MASTER AS TM2 ON TM2.T_ID = MM.TEAM2_ID

Answer 3

We can achieve the same with self join. Set up:

create table MATCH_MASTER(M_ID number, TEAM1_ID number, TEAM2_ID number );
create table TEAM_MASTER(T_ID number, T_NAME varchar2(100) );
insert into MATCH_MASTER values (1, 1, 2);
insert into TEAM_MASTER values (1, 'A');
insert into TEAM_MASTER values (2, 'B');
commit;

The query with self join:

select master.m_id,
      team1.t_name team1_id,
      team2.t_name team2_id
from match_master master,
     team_master team1,
     team_master team2
where master.team1_id = team1.t_id
  and master.team2_id = team2.t_id;