Join two tabels - Oracle SQL

Question

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.

Answer 1

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