Oracle SQL - left join

Question

I have below 3 tables: Table1:

Table2:

Table3:

For the cust_id and sub_id present in table1 i want to get the corresponding id1 & id2 and id3 from table 2 and table 3 respectively.

but below query is not giving expected result.

Query:

select t1.cust_id, t1.subscr, t2.id1, t2.id2, t3.id3
from table1 as t1
  left join table2 t2 on t1.cust_id = t2.cust_id and t1.subscr = t2.subscr
  left join table3 on t1.cust_id = t3.cust_id and t1.subscr = t3.subscr

query result:

Expected result:

How to get the expected result?

Answer 1

With your sample data to get your expected result you should "create" columns to link the rows of t2 and t3. I don't know if it is applicable to your real data. If we create columns with row numbers (RN) in tables t2 and t3 using order by all columns respectively then there will exist a link between the rows to connect the rows in a way described at the end of TomS's answer. Here is the code:

WITH
    t1 AS
        (
            SELECT 123 "CUST_ID",   987 "SUBSCR" FROM DUAL
        ),
    t2 AS
        (
            SELECT 123 "CUST_ID",   987 "SUBSCR", 1002 "ID1", 2005 "ID2" FROM DUAL UNION ALL
            SELECT 123 "CUST_ID",   987 "SUBSCR", 2002 "ID1", 3005 "ID2" FROM DUAL 
        ),
  t2a AS
    (
      Select  t2.CUST_ID, t2.SUBSCR, t2.ID1, t2.ID2, ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY t2.CUST_ID, t2.SUBSCR, t2.ID1, t2.ID2) "RN" From t2    
    ),
    t3 AS
        (
            SELECT 123 "CUST_ID",   987 "SUBSCR", 5001 "ID3" FROM DUAL UNION ALL
            SELECT 123 "CUST_ID",   987 "SUBSCR", 5002 "ID3" FROM DUAL
        ),
  t3a AS
    (
      Select  t3.CUST_ID, t3.SUBSCR, t3.ID3, ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY t3.CUST_ID, t3.SUBSCR, t3.ID3) "RN" From t3
    )
SELECT DISTINCT
    t1.CUST_ID "CUST_ID",
    t1.SUBSCR "SUBSCR",
    Nvl(t2.ID1, 0) "ID1",
    Nvl(t2.ID2, 0) "ID2",
    Nvl(t3.ID3, 0) "ID3"
FROM
    t1
LEFT JOIN
  t2a t2 ON (t2.CUST_ID = t1.CUST_ID And t2.SUBSCR = t1.SUBSCR)  
LEFT JOIN  
  t3a t3 ON(t3.CUST_ID = t1.CUST_ID And t3.SUBSCR = t1.SUBSCR And t3.RN = t2.RN)

Two CTEs that are created (t2a and t3a) are used in the main SQL instead of tables t2 and t3. With this sample data there would be the same result if we use INNER instead of LEFT join. You, knowing your actual data and needs, would have to adjust this code to meet your goals.
The result is...

--     CUST_ID     SUBSCR        ID1        ID2        ID3
--  ---------- ---------- ---------- ---------- ----------
--         123        987       1002       2005       5001 
--         123        987       2002       3005       5002