在SQL查询中连接4个表 [英] Join 4 tables in SQL query

问题描述

在我的数据库中有4个关系表，并希望加入他们获得总值...例如我有朋友，家人，下一个和熟人，并希望加入这些获得一个全部值。 >

表格格式如下：

朋友

  id追随者
 --------------------- 
 1 2 3 
 2 4 5 
  

家庭 b
$ b

  id追随者
 --------------------- 
 1 5 6 
 2 7 8 
  

关注 p>

  id跟随者
 --------------------- 
 1 9 10 
 2 11 12 
  

熟人

  id追随者
 ------------------ --- 
 1 13 14 
 2 15 16 
  

加入所有4个表...

 选择以下
 FROM friends 
 INNER JOIN family ON friends。 following = family.following 
 INNER JOIN following ON friends.following = follow.following 
 INNER JOIN acquaintances ON friends.following = acquaintances.following 
 WHERE follower ='id'
  

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

解决方案

您的当前查询将只列出一个结果，如果所有表有一个链接与您的朋友表。我相信你更喜欢这样的东西

  SELECT after 
 FROM friends 
 WHERE follower = id'
 UNION ALL 
 SELECT after 
 FROM family 
 WHERE follower ='id'
 UNION ALL 
选择以下
从以下
 WHERE follower ='id'
 UNION ALL 
选择以下
从熟人
 WHERE follower ='id'
  
 
 pre> 
 
 
 code> SELECT after 
 FROM（
 SELECT以下，从者FROM friends 
 UNION ALL SELECT以下，从属FROM family 
 UNION ALL SELECT关注，追随者FROM以下
 UNION ALL SELECT following，follower FROM acquaintances 
）AS f 
 WHERE follower ='id'
  
  UNION  
 
 
 UNION用于将多个SELECT语句
的结果合并到单个结果集中。
 
 
 
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.

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...
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
解决方案
Your current query will only list a result if all tables have 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.


                        
这篇关于在SQL查询中连接4个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！