-1

I have two tables and I would like to join them. Below there are the examples of tables and the result table:

Table A

ID PLACE R_ID M_ID
1 PLACE_1 51 53
2 PLACE_2 52 54

Table B

S_ID NAME
51 A
52 B
53 C
54 D

Output Table

ID PLACE R_NAME M_NAME
1 PLACE_1 A C
2 PLACE_2 B D

I tried query:

SELECT
    id,
    place,
    name as r_name
FROM
    table_a 
    LEFT JOIN table_b ON r_id = s_id

Query result

ID PLACE R_NAME
1 PLACE_1 A
2 PLACE_2 B

But I don't know how to join next column m_name.

PieterPrr
  • 1
  • 1
  • "join them" & "join next column" is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS [Query to get name from a table with 2 ID columns](https://stackoverflow.com/q/4308644/3404097) [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/q/27682228/3404097) What is a self join for? (in english)](https://stackoverflow.com/a/37384306/3404097) – philipxy May 16 '23 at 14:32
  • (Clearly.) When clear this will be a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy May 16 '23 at 14:33

3 Answers3

0

You can do it as follows :

with cte_r as ( 
  SELECT a.ID, a.PLACE, b.NAME
  FROM tableA a
  INNER JOIN tableB b on a.R_ID = b.S_ID
),
cte_m as (
  SELECT a.ID, a.PLACE, b.NAME
  FROM tableA a
  INNER JOIN tableB b on a.M_ID = b.S_ID
)
SELECT r.ID, r.PLACE, r.NAME as R_NAME, m.NAME as M_NAME
FROM cte_r r
LEFT JOIN cte_m m on r.ID = m.ID and r.PLACE = m.PLACE

Result :

ID  PLACE   R_NAME  M_NAME
1   PLACE_1 A       C
2   PLACE_2 B       D

Demo here

SelVazi
  • 10,028
  • 2
  • 13
  • 29
0

Join twice to the same table and use table aliases to distinguish between the two:

SELECT id,
       place,
       r.name as r_name,
       m.name as m_name
FROM   table_a a
       LEFT JOIN table_b r ON a.r_id = r.s_id
       LEFT JOIN table_b m ON a.m_id = m.s_id

Which, for the sample data:

CREATE TABLE Table_A (ID, PLACE, R_ID, M_ID) AS
SELECT 1, 'PLACE_1', 51, 53 FROM DUAL UNION ALL
SELECT 2, 'PLACE_2', 52, 54 FROM DUAL;

CREATE TABLE Table_B (S_ID, NAME) AS
SELECT 51, 'A' FROM DUAL UNION ALL
SELECT 52, 'B' FROM DUAL UNION ALL
SELECT 53, 'C' FROM DUAL UNION ALL
SELECT 54, 'D' FROM DUAL;

Outputs:

ID PLACE R_NAME M_NAME
1 PLACE_1 A C
2 PLACE_2 B D

fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117
0

Also do it using subquery:

SELECT a.id,
   a.place,
   (SELECT name FROM table_b r WHERE a.r_id = r.s_id) AS r_name,
   (SELECT name FROM table_b m WHERE a.m_id = m.s_id) AS m_name
FROM table_a a;
shajin
  • 29
  • 4