Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

sql - Getting extra rows - After joing the 3 tables using Left Join

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...