MySQL 在 SQL 查询中加入 4 个表

Join 4 tables in SQL query

提问by user946742

Have 4 relationship tables in my database and want to join them to get the total value... for instance i have friends, family, following and acquaintances and want to join these to get an "ALL" value.

我的数据库中有 4 个关系表，并希望加入它们以获得总价值……例如，我有朋友、家人、追随者和熟人，并希望加入这些以获得“全部”价值。

The table formats are as such:

表格格式如下：

Friends

朋友们

id follower following
---------------------
1     2      3
2     4      5

Family

家庭

id follower following
---------------------
1     5      6
2     7      8

Following

下列的

id follower following
---------------------
1     9      10
2     11     12

Acquaintances

熟人

id follower following
---------------------
1     13     14
2     15     16

Is the correct query to join all 4 tables...

加入所有 4 个表的正确查询是...

SELECT following
FROM   friends
       INNER JOIN family ON friends.following=family.following
       INNER JOIN following ON friends.following=following.following
       INNER JOIN acquaintances ON friends.following=acquaintances.following
WHERE  follower='id'

Basically I want to join and retrieve the "following" value from all four tables where id= my id

基本上我想从所有四个表中加入并检索“以下”值，其中 id= my id

回答by Lieven Keersmaekers

Your current query will only list a result if all tableshave a link with your friends table. I believe you are more looking for something like this

如果所有表都与您的朋友表有链接，则您当前的查询只会列出结果。我相信你更在寻找这样的东西

SELECT following
FROM   friends
WHERE  follower='id'
UNION ALL 
SELECT following
FROM   family 
WHERE  follower='id'
UNION ALL 
SELECT following
FROM   following
WHERE  follower='id'
UNION ALL 
SELECT following
FROM   acquaintances 
WHERE  follower='id'

or a bit nicer to read and easier to adjust at the cost of some performance

或者以牺牲一些性能为代价更好地阅读和更容易调整

SELECT following 
FROM   (
        SELECT following, follower FROM friends
        UNION ALL SELECT following, follower FROM family 
        UNION ALL SELECT following, follower FROM following
        UNION ALL SELECT following, follower FROM acquaintances 
       ) AS f
WHERE  follower='id'

UNION

联盟

UNION is used to combine the result from multiple SELECT statements into a single result set.

UNION 用于将多个 SELECT 语句的结果组合成一个结果集。

回答by Alexander van Oostenrijk

You'll want to do this:

你会想要这样做：

SELECT following FROM friends WHERE follower='id'
UNION
SELECT following FROM family WHERE follower='id'
UNION
SELECT following FROM followers WHERE follower='id'
UNION
SELECT following FROM acquaintances WHERE follower='id'

Which will yield

哪个会产生

following
---------------------
3
4
6
8
10
12
14
16

It might be better to stick friends, family, followers and acquaintances all in the same table though, unless the differences between these categories is big enough to merit their own tables.

不过，最好将朋友、家人、追随者和熟人都放在同一张桌子上，除非这些类别之间的差异大到足以配得上他们自己的桌子。

回答by che

Not really. If I simplify your example to friends and family, joining them will return only people who are both friends and family, and allow you to extract ids from all tables in one row, which is pretty much useless.

并不真地。如果我将您的示例简化为朋友和家人，加入他们只会返回既是朋友又是家人的人，并允许您从一行中的所有表中提取 id，这几乎没有用。

What you want is a union, as the other answer suggest. However, a better choice might be to have one table of relationships that will have a special (enum?) column to define its type, instead of one table for every kind of relationship.

你想要的是一个工会，正如另一个答案所暗示的那样。但是，更好的选择可能是拥有一个关系表，该表将有一个特殊的（枚举？）列来定义其类型，而不是每个关系都有一个表。

回答by aifarfa

you should redesign entity table. friends, family, acquaintance can be separated by fields

你应该重新设计实体表。朋友、家人、熟人可以被字段隔开

id follower following type
---------------------------------
1     5      6        Family
2     7      8        Friends
3     13     14       Acquaintance 
4     15     16       Friends

then you can filter, order or grouping by type

然后你可以过滤，排序或分组 type

回答by Rufinus

as stated in my comment for this situation and depending on the amount of data you have in your x-tables i would use union:

正如我对这种情况的评论中所述，根据您在 x 表中拥有的数据量，我将使用联合：

(SELECT following FROM friends WHERE follower='id')
UNION
(SELECT following FROM family WHERE follower='id')
UNION
(SELECT following FROM following WHERE follower='id')
UNION
(SELECT following FROM acquaintances WHERE follower='id')