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:

Kindly help to get the expected result

CodePudding user response：

I think that what you get is correct. For your combination of cust id and subscr in table1, there are two matches in table 2, which results in two rows as a result of the first LEFT JOIN operation of your query. Now, you are using this table for your second LEFT JOIN operation. Those two lines are different and each one matches each row of the third table which results in a total of four lines showing all possible combinations of id1, id2 and id3.

Or, think about the following question: Why should your values 1002 and 2005 for id1 and id2 in table 2, line 1 only be matched with line 1 of table 3 and your id3 = 5001, what is presumably what you want? WHY should the database system do that? There is no such function of match first line of one table with first line of another, as the order of display depends on many things (like a sorting or the order in which data was inserted) but is not a prooerty of the data itself. The database does not know of any difference between the first row and the second and treats all the rows identically which means there's no reason why why line 2 of table 3 should only be connected to line 2 of table 2.

CodePudding user response：

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