1

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?

2

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;
1

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
  • 1
    No, it's not (exactly) the same thing. The scalar sub-select is effectively an outer join. – a_horse_with_no_name Oct 31 '18 at 12:42
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.