ORACLE/SQL - 连接 3 个并非全部互连的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5760020/
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
ORACLE/SQL - Joining 3 tables that aren't all interconnected
提问by dscl
I need help joining 3 tables where they are not all interconnected
我需要帮助加入 3 个并非全部相互关联的表
So lets say I have tables A, B, C
所以假设我有表 A、B、C
here are the relations
这是关系
A.type = B.type
A.type = B.type
A.model = C.model
A.model = C.model
What I need to do is inner join A and B and return all the matched A records. Next I need to pull the records from C that match on the prior join.
我需要做的是内连接 A 和 B 并返回所有匹配的 A 记录。接下来，我需要从 C 中提取与先前连接匹配的记录。
Or in other words all the records in C that are in A where A is in B
或者换句话说，C 中的所有记录都在 A 中，而 A 在 B 中
Hope that makes sense. Sorry for no data examples.
希望这是有道理的。抱歉没有数据示例。
I have tried this
我试过这个
select
c.*
from
c, a, b
where
c.model_ = a.model_
and a.type_ = b.type_
but receive this message 'Errors: Query has MERGE JOIN CARTESIAN. You must correct where-clause to properly join tables in the select statement.'
但收到此消息“错误：查询具有 MERGE JOIN CARTESIAN。您必须更正 where 子句才能在 select 语句中正确连接表。
回答by J?rn Horstmann
I know this is a matter of style but in my opinion ansi style joins make this much clearer:
我知道这是一个风格问题，但在我看来，ansi 风格的加入使这一点更加清晰：
SELECT c.*
FROM c
JOIN a ON a.model = c.model
JOIN b on b.type = a.type
In case you have multiple matching elements in a or b, this query will return duplicates. You can either add a DISTINCT or rewrite it as an EXISTS query:
如果 a 或 b 中有多个匹配元素，此查询将返回重复项。您可以添加 DISTINCT 或将其重写为 EXISTS 查询：
SELECT *
FROM c
WHERE EXISTS (SELECT 1
FROM a
JOIN b ON b.type = a.type
WHERE a.model = c.model)
I think this should also give the same result, as long as there are no NULL values in model:
我认为这也应该给出相同的结果，只要模型中没有 NULL 值：
SELECT *
FROM c
WHERE c.model IN (SELECT a.model
FROM a
JOIN b ON b.type = a.type)
回答by Randy
simply also join to C with an AND condition
也只需使用 AND 条件加入 C
回答by Rajesh Chamarthi
Without the data samples, it is difficult to understand the actual problem and validate the query.
如果没有数据样本，就很难理解实际问题并验证查询。
did you try..
你试过了吗..
Select c.*
from a,b,c
where a.type = b.type
and a.model = c.model;
Are your losing some records or seeing extra records that you don't expect to see?
您是否丢失了一些记录或看到了您不希望看到的额外记录？
The above query will only give you records in c that are in a and where a records match the criterion in b.
上面的查询只会为您提供 c 中 a 中的记录，并且 a 记录与 b 中的条件匹配。
