Getting extra rows - After joing the 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 select is returning 7649 rows. Could you please help me in this? thanks in advance

Answer 1

table1 may have only 7622 rows, but if tbl2 has more than one row with the same ACCOUNT value, or if tbl3 has more than one row where the product matches, you'll get more rows in the result set. You're effectively "multiplying" each of the tables.

---

EDIT: OK, an example.

Suppose tbl1 has only 1 row, and that the "ACCOUNT" is 1 and the "product" is 2. (I don't know what actual values are in the table; it does not matter).

Now suppose that tbl2 has 2 rows where "ACCOUNT" is 1. Straight away, you're going to get at least 2 rows in your results, because tbl1 will match 2 rows in tbl2.

Now if tbl3 has 2 rows where "product" is 2, you'll get 4 rows in the results, because each of the 2 results above will match 2 rows in tbl3.

So hopefully you can see why you're getting more rows than you expected. What you choose to do about it is a different matter, and depends on whether the fact that there are multiple matches in tbl2 and tbl3 indicate a problem with the data.

Answer 2

When you JOIN two or more table together, you effectively get a cartesian product for these tables to which a filter stated in the JOIN condition is applied.

This is more obvious when you use an obsolete implicit JOIN syntax.

The LEFT JOIN guarantees that you get no less rows than the leftmost table contains, i. e. each row from the leftmost table is returned at least once.

You can still get more rows, if the filter is not a one-to-one row mapping.

In your case:

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 = ' '

either acccount or product are not unique in b or c.

For these rows:

a.account

1
2
3

b.account  b.description

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

, the JOIN will return the following:

a.account b.account b.description

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

, giving you more rows than either of the tables contains.

To just pick the first matching description from either table, use this:

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 = ' '

To update, just wrap the query into an inline view:

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 3

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

One option to correct the issue is to group your joined tables after joining them:

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 your 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 = ' '