如何在多表连接中引用父表? [英] How do I reference parent table in a multi table join?

问题描述

这是我的表的表示.表 a 是一种父级(id 是主键).b 和 c 有不同的行数(它的 pid 是对 parent 的引用).

This is a representation of my table(s). Table a is sort of a parent (id being the primary key). b and c have varying number of rows (its pid is a reference to parent).

mysql> select * from a;
+------+------+
| id   | name |
+------+------+
| 1    | Ruby |
+------+------+

mysql> select * from b;
+------+------+
| pid  | name |
+------+------+
| 1    | Java |
| 1    | C++  |
+------+------+

mysql> select * from c;
+------+------+-------+
| id   | pid  | name  |
+------+------+-------+
| 100  | 1    | Ember |
+------+------+-------+

我正在努力实现以下任一目标:

I am trying to achieve either of the below:

+------+------+------+------+------+------+-------+
| id   | name | pid  | name | id   | pid  | name  |
+------+------+------+------+------+------+-------+
| 1    | Ruby | 1    | Java | 100  | 1    | Ember |
| 1    | Ruby | 1    | C++  | NULL | NULL | NULL  |
+------+------+------+------+------+------+-------+

或

+------+------+------+------+------+------+-------+
| id   | name | pid  | name | id   | pid  | name  |
+------+------+------+------+------+------+-------+
| 1    | Ruby | 1    | Java | 100  | 1    | Ember |
| NULL | NULL | 1    | C++  | NULL | NULL | NULL  |
+------+------+------+------+------+------+-------+

左外连接并没有完全产生我想要的(因为第二个连接是在第一个连接的结果上完成的，因此产生了 2 行)；

Left Outer join doesn't quite produce what I wanted (because the 2nd join is done on the result of the first join thus producing 2 rows);

mysql> select *
    ->   from A
    ->   left outer join B ON a.id = b.pid
    ->   left outer join C ON a.id = c.pid;
+------+------+------+------+------+------+-------+
| id   | name | pid  | name | id   | pid  | name  |
+------+------+------+------+------+------+-------+
| 1    | Ruby | 1    | Java | 100  | 1    | Ember |
| 1    | Ruby | 1    | C++  | 100  | 1    | Ember |
+------+------+------+------+------+------+-------+

推荐答案

您得到的结果在意料之中.您应该为 C++ 设置一个不同的 id，这将导致您想要的结果.

The results you get are expected. You should have a different id for C++ and that would lead to the results you want.

您需要在 B 表中添加您将在 C 表中使用的 ID 列(作为外键)，以便您可以过滤结果.

You need to add and ID column in the B table that you would use in the C table (as a foreign key) so that you can filter the results.

修复此数据库设计问题将帮助您解决此问题，因为您当前的设计存在缺陷.

Fixing this database design issue will help you solve this problem as your current design is flawed.

结果查询应如下所示:

SELECT *
FROM A
   LEFT OUTER JOIN B ON A.id = B.pid
   LEFT OUTER JOIN C ON A.id = B.pid AND C.newForeignKeyId=B.newId;

另外，我建议使用自动递增的 IDENTITY id 列，这样您就不会遇到完整性问题.

Also, I would recommend using IDENTITY id columns that increment automatically so that you don't run into integrity problems.

这篇关于如何在多表连接中引用父表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！