Getting additional rows - After connecting 3 tables using Left Join

Question

SELECT (b.descr || ' - ' || c.descr) description
  FROM tbl1 a LEFT JOIN tbl2 b ON a.ACCOUNT = b.ACCOUNT 
       LEFT JOIN tbl3 c ON a.product = c.product
 WHERE a.descr50 = ' ' ;  

table1 has only 7622 rows with descr50 = '', but this selection returns 7649 rows. Could you help me with this? thank you in advance

Answer 1

When you JOINcombine two or more tables, you actually get Cartesian products for these tables, to which the filter specified in the condition applies JOIN.

This is more obvious if you use obsolete implicit syntax JOIN.

LEFT JOINensures that you get at least rows than the leftmost table contains, i. E. Each row from the leftmost table is returned at least once.

, .

:

SELECT  (b.descr || ' - ' || c.descr) description
FROM    tbl1 a
LEFT JOIN
        tbl2 b
ON      b.ACCOUNT = a.ACCOUNT
LEFT JOIN
        tbl3 c
ON      c.product = a.product
WHERE  a.descr50 = ' '

acccount, product b c.

:

a.account

1
2
3

b.account  b.description

1          Account 1
2          Account 2 - old
2          Account 2 - new

JOIN :

a.account b.account b.description

1         1          Account 1
2         2          Account 2 - old
2         2          Account 2 - new
3         NULL       NULL

, .

, :

SELECT  (
        SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
        FROM    tbl2 b
        WHERE   b.account = a.account
                AND rownum = 1
        ) || ' - ' ||
        (
        SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
        FROM    tbl3 c
        WHERE   c.product= a.product
                AND rownum = 1
        ) description
FROM    tbl1 a
WHERE   a.descr50 = ' '

, :

UPDATE  (
        SELECT  (
                SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
                FROM    tbl2 b
                WHERE   b.account = a.account
                        AND rownum = 1
                ) || ' - ' ||
                (
                SELECT  FIRST_VALUE(descr) OVER (ORDER BY descr)
                FROM    tbl3 c
                WHERE   c.product= a.product
                        AND rownum = 1
                ) description
        FROM    tbl1 a
        WHERE   a.descr50 = ' '
        )
SET     descr50 = description

Answer 2

table1 7622 , tbl2 ACCOUNT, tbl3 , , . "" .

---

: , .

, tbl1 1 "ACCOUNT" 1, "product" 2. ( , , ).

, tbl2 2 , "ACCOUNT" 1. , 2 , tbl1 2 tbl2.

, tbl3 2 , "" 2, 4 , 2 2 tbl3.

, , , , . , , - , , , tbl2 tbl3, .

Answer 3

As a test to determine where additional rows come from, try adding more fields to the SELECT statement from the joined tables and look at the returned data.

One way to fix the problem is to group the joined tables after they are joined:

SELECT (b.descr || ' - ' || c.descr) description
    FROM tbl1 a 
    LEFT JOIN tbl2 b ON a.ACCOUNT = b.ACCOUNT 
    LEFT JOIN tbl3 c ON a.product = c.product
    WHERE a.descr50 = ' '
    GROUP BY b.descr, c.descr

Another option would be to group the tbl2 and tbl3 tables before joining them:

SELECT (b.descr || ' - ' || c.descr) description
    FROM tbl1 a 
    LEFT JOIN 
    (
        SELECT descr, ACCOUNT 
            FROM tbl2 
            GROUP BY descr, ACCOUNT
    ) AS b
        ON a.ACCOUNT = b.ACCOUNT
    LEFT JOIN 
    (
        SELECT descr, product 
            FROM tbl3 
            GROUP BY descr, product 
    ) AS c
        ON a.product = c.product 
    WHERE a.descr50 = ' '