Postgresql：如何加入多个交叉引用表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17874873/
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
Postgresql : How to join with multiple cross-reference table?
提问by Samy-DT
I've seen a lot of post on multiple JOIN but it didn't help me in my case.
我在多个 JOIN 上看到了很多帖子，但在我的情况下它没有帮助我。
Consider that I have three tables and two cross-reference tables. That's the difference with the others posts where they had multiple tables but one cross-reference table in the FROM.
考虑到我有三个表和两个交叉引用表。这是与其他帖子的不同之处，他们有多个表，但在 FROM 中有一个交叉引用表。
Table1 -> cross-ref1 <- table2 -> cross-ref2 <- table3
Table1 -> cross-ref1 <- table2 -> cross-ref2 <- table3
My version of Postgresql is : 9.0.11, and I'm working with W7 64 bits.
我的 Postgresql 版本是：9.0.11，我正在使用 W7 64 位。
My request is the following stuff :
我的要求是以下内容：
Select [columns] from cross-ref1, cross-ref2
INNER JOIN table1 ON table1.id_table1=cross-ref1.ref_id_table1
INNER JOIN table2 ON table2.id=cross-ref1.ref_id_table2
INNER JOIN table2 On table2.id_table2=cross-ref2.ref_id_table2
INNER JOIN table3 ON table3.id_table3=cross-ref2.ref_id_table3
The error message is : "Table name is specified more than once."
错误消息是：“多次指定表名。”
Can you explain me the error ?
你能解释一下错误吗？
Thanks
谢谢
回答by Craig Ringer
Cross-reference tables need separate columnsfor each side of the reference. An xref table with just one column makes no sense, as it can only refer to rows with the same ID on each side.
交叉引用表需要为引用的每一侧使用单独的列。只有一列的外部参照表没有意义，因为它只能引用每一侧具有相同 ID 的行。
A typical setup would be:
一个典型的设置是：
CREATE TABLE a (
id integer primary key,
avalue text not null
);
CREATE TABLE b (
id integer primary key,
bvalue text not null
);
CREATE TABLE ab (
a_id integer references a(id),
b_id integer references b(id),
PRIMARY KEY(a_id, b_id)
);
Given sample data:
给定样本数据：
INSERT INTO a(id, avalue) VALUES
(1, 'a1'), (2, 'a2'), (3, 'a3'), (4, 'a4');
INSERT INTO b(id, bvalue) VALUES
(41, 'b1'), (42, 'b2'), (43, 'b3');
INSERT INTO ab(a_id, b_id) VALUES
(1, 41), (1, 42), (2, 43);
You'd find the pairings of aand bwith:
你会找到的配对a，并b用：
SELECT avalue, bvalue
FROM a
INNER JOIN ab ON (a.id = ab.a_id)
INNER JOIN b ON (b.id = ab.b_id);
The crucial thing here is that you're joining on ab.a_idon the aside, and ab.b_idon the bside. Observe demo here: http://sqlfiddle.com/#!12/3228a/1
这里的关键是，你要加入上ab.a_id的a一侧，并ab.b_id在b侧。在这里观察演示：http: //sqlfiddle.com/#!12/3228a/1
This is pretty much "many-to-many table relationships 101", so it might be worth doing some more study of introductory SQL and relational database tutorials and documentation.
这几乎是“多对多表关系 101”，因此可能值得对介绍性 SQL 和关系数据库教程和文档进行更多研究。
回答by Oskar
You can't use the same table name twice (table2). In this case you need to use aliases like t1, t2a, t2b, ...
您不能两次使用相同的表名 (table2)。在这种情况下，您需要使用别名，如 t1、t2a、t2b、...
SELECT
...
FROM
table1 AS t1
INNER JOIN table2 AS t2a
ON t2a.id= ...
INNER JOIN table2 AS t2b
ON t2b.id= ...
INNER JOIN table3 AS t3
ON t3.id= ...
...
Now you can join whatever you want, how many times you want etc.
现在你可以加入任何你想要的，你想要多少次等等。
回答by Tomas Greif
You have to explain what result you want to have. For example the following SQL is valid from syntax point of view, not sure about business point of view:
你必须解释你想要什么结果。例如以下 SQL 从语法的角度来看是有效的，但不确定业务角度：
-- this will create sample data with 5 tables
with
crossref1(ref_id) as (VALUES (1),(2),(3)),
crossref2 (ref_id) as (VALUES (2),(3),(4)),
table1 (ref_id) as (VALUES (3),(4),(5)),
table2 (ref_id) as (VALUES (1),(2),(3)),
table3 (ref_id) as (VALUES (1),(2),(3))
-- valid SQL based on your example
select * from
crossref1
cross join crossref2
join table1 on table1.ref_id=crossref1.ref_id
join table2 as t2_1 on t2_1.ref_id=crossref1.ref_id
join table2 as t2_2 on t2_2.ref_id=crossref2.ref_id
join table3 on table3.ref_id=crossref2.ref_id
With your SQL there are two problems:
你的 SQL 有两个问题：
- You have two references to table2, you have to add alias
- You have to use
cross joinsyntax instead of,
- 你有两个对table2的引用，你必须添加别名
- 你必须使用
cross join语法而不是,
If you would like to understand how withworks (how I created sample data), PostgreSQL has excellent documentationon this.
如果您想了解with工作原理（我如何创建示例数据），PostgreSQL有关于此的出色文档。
