连接3个表Oracle SQL [英] Joining 3 tables Oracle SQL

问题描述

我在下面列出了3张桌子:

I have 3 tables listing below:

Table_A:

order_number | header_id
        123  | 80001

Table_B

   header_id | line_id | quantity
       80001 | 10001   | 1
       80001 | 10002   | 3
       80001 | 10003   | 5

Table_C

   header_id | line_id | hold_price_id | released_flag
       80001 |   10001 | 2001          | Y
       80001 |   10002 | 2002          | Y
       80001 |   10003 | 2003          | N

我写了一个查询，如下所示:

I wrote a query as shown below:

SELECT A.order_number, A.header_id, B.line_id, B.quantity, C.hold_price_id, C.released_flag
FROM Table_A a,
     Table_B b,
     Table_C c
WHERE a.header_id = b.header_id
AND   c.line_id = b.line_id
AND   a.order_number = '123';

我的愿望输出如下所示:

My desire output is as shown below:

   order_number | header_id | line_id | quantity | hold_price_id | released_flag
            123 |     80001 |   10001 | 1        | 2001          | Y
            123 |     80001 |   10002 | 3        | 2002          | Y
            123 |     80001 |   10003 | 5        | 2003          | N

但是查询显示以下结果:

However the query show me the below result:

order_number | header_id | line_id | quantity      | hold_price_id | released_flag
         123 |     80001 |   10001 | 1             | 2001          | Y
         123 |     80001 |   10001 | 3             | 2002          | Y
         123 |     80001 |   10001 | 5             | 2003          | N
         123 |     80001 |   10002 | 1             | 2001          | Y
         123 |     80001 |   10002 | 3             | 2002          | Y
         123 |     80001 |   10002 | 5             | 2003          | N
         123 |     80001 |   10003 | 1             | 2001          | Y
         123 |     80001 |   10003 | 3             | 2002          | Y
         123 |     80001 |   10003 | 5             | 2003          | N

我的查询有问题吗?请指教.

Is it something wrong on my query? Please advice.

谢谢！

推荐答案

您尚未加入所有通用键，因此可以得到笛卡尔结果.您需要使用标题ID将a加入到c中，

You haven't joined all of the common keys, so you are getting Cartesian results. You needs to join a to c with header id, like so

SELECT A.order_number, A.header_id, B.line_id, B.quantity, C.hold_price_id, C.released_flag
FROM Table_A a,
     Table_B b,
     Table_C c
WHERE a.header_id = b.header_id
AND   c.line_id = b.line_id
AND   a.header_id = c.header_id
AND   a.order_number = '123';

这篇关于连接3个表Oracle SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！