加入 3 个表 Oracle SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29337086/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining 3 tables Oracle SQL
提问by Law
I have 3 tables listing below:
我在下面列出了 3 个表：
Table_A:
表_A：
order_number | header_id
123 | 80001
Table_B
表_B
header_id | line_id | quantity
80001 | 10001 | 1
80001 | 10002 | 3
80001 | 10003 | 5
Table_C
表_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.
我的查询有问题吗？请指教。
Thank you!
谢谢！
回答by Gordon Linoff
You need to learn to use proper explicit joinsyntax. A simple rule: never use commas in the fromclause. Alwaysuse explicit joins:
您需要学习使用正确的显式join语法。一个简单的规则：永远不要在from子句中使用逗号。 始终使用显式joins：
SELECT A.order_number, A.header_id, B.line_id, B.quantity,
C.hold_price_id, C.released_flag
FROM Table_A a JOIN
Table_B b
ON a.header_id = b.header_id JOIN
Table_C c
ON c.header_id = b.header_id AND c.line_id = b.line_id
WHERE a.order_number = '123';
回答by nomistic
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
你还没有加入所有的公共键，所以你得到了笛卡尔结果。您需要将 a 与标头 id 连接到 c，就像这样
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';
回答by k keerthi
SELECT a.order_number, a.header_id, b.line_id, b.quantity ,c.hold_price_id,c.released_flag FROM Table_A a cross JOIN Table_B b cross join table_C c;
SELECT a.order_number, a.header_id, b.line_id, b.quantity ,c.hold_price_id,c.released_flag FROM Table_A a cross JOIN Table_B b cross join table_C c;
ORDER_NUMBER HEADER_ID LINE_ID QUANTITY HOLD_PRICE_ID RELEASED_F
ORDER_NUMBER HEADER_ID LINE_ID QUANTITY HOLD_PRICE_ID RELEASED_F
123 80001 10001 1 2001 Y
123 80001 10002 3 2001 Y
123 80001 10003 5 2001 Y
123 80001 10001 1 2002 Y
123 80001 10002 3 2002 Y
123 80001 10003 5 2002 Y
123 80001 10001 1 2003 N
123 80001 10002 3 2003 N
123 80001 10003 5 2003 N
9 rows selected.
已选择 9 行。
