php - MySQL通过关系表连接

Question

您好，我似乎找不到编写此查询的正确方法。我有两个实体网站和客户，以及一个通过它们的 id 字段将它们关联起来的表。

这是一个多对多的关系。即一个网站可以有多个客户端，一个客户端可以有多个网站。

我正在尝试编写一个查询，返回所有网站以及属于它们的客户。我想返回所有网站，即使它们没有关联的客户。这是我目前正在处理的查询:

三个表分别是ost_sites = websites, ost_site_auth = relational table, ost_clients = clients

SELECT 
    ost_sites.site_id, 
    ost_sites.name,
    ost_sites.site_url,
    ost_site_auth.site_id,
    ost_site_auth.client_id 
    ost_clients.client_id,
    CONCAT_WS(" ", ost_clients.lastname, ost_clients.firstname) as name,
FROM ost_sites
LEFT JOIN (ost_site_auth, ost_clients) 
    ON (ost_sites.site_id=ost_site_auth.site_id 
        AND ost_site_auth.client_id=ost_clients.client_id)
GROUP BY ost_sites.name

我得到了一个结果集，但它没有返回所有站点，而且所有行都没有与之关联的客户。

非常感谢您的帮助!

编辑:

以下是表格的列:

发布网站

site_id |    name    | site_url
1          facebook    facebook.com
2          twitter     twitter.com
3          tubmblr     tumblr.com
4          google      google.com

ost_site_auth

(注意授权列表中没有 site_id = 3)

id |   site_id    | client_id
1        1             1
2        1             2
3        2             1 
4        2             2
5        4             1
6        4             4

ost_client

client_id  |  firstname  |  lastname
1              wilma         flintstone
2              bam           bam
3              fred          flintstone
4              barney        rubble

预期输出:

site_id |    name    |    site_url    |      client_name     |
1          facebook    facebook.com        wilma flintstone
1          facebook    facebook.com        bam bam
2          twitter     twitter.com         wilma flintstone
2          twitter     twitter.com         bam bam
4          google      google.com          wilma flintstone
4          google      google.com          barney rubble
3          tumblr      tumlr.com           NULL

Answer 1

你的加入看起来有点不正常......试试这个

SELECT 
    ost_sites.site_id, 
    ost_sites.name,
    ost_sites.site_url,
    ost_site_auth.site_id,
    ost_site_auth.client_id 
    ost_clients.client_id,
    CONCAT_WS(" ", ost_clients.lastname, ost_clients.firstname) as name

FROM ost_sites

LEFT OUTER JOIN ost_site_auth 
    ON ost_sites.site_id=ost_site_auth.site_id 

LEFT OUTER JOIN ost_clients
    ON ost_site_auth.client_id=ost_clients.client_id

ORDER BY ost_sites.name

---

让我试着为你解释一下......

• 我们从 ost_sites 表开始，我们想要所有结果，无论其他表是否匹配。
• 然后，我们对表ost_site_auth 执行左外连接。这意味着如果来自 ost_site_auth 的内容与 ost_sites 中的内容不匹配，则不会返回。但是，由于 left outer 部分，ost_sites 中与 ost_site_auth 中不匹配的内容将被返回。
• 接下来，我们为 ost_clients 重复左外连接。

---

---

不确定您想要什么...假设我们在表中表示了这些数据:

• 站点 #1 没有客户
• 站点 #2 有一个客户:A
• 站点 #3 有两个客户:B、C
• 站点 #4 有三个客户:D、E、F
• 站点 #5 没有客户
• 客户 G 和 H 没有关联网站

查询一

      SELECT 
          ost_sites.site_id as SITE,   
          ost_clients.client_id as CLIENT

      FROM ost_sites

      LEFT OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      LEFT OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id, ost_clients.client_id

那会(基本上)返回

SITE     CLIENT 
1        NULL
2        A
3        B
3        C
4        D
4        E
4        F
5        NULL

查询二

      SELECT 
          ost_sites.site_id as SITE,   
          ost_clients.client_id as CLIENT

      FROM ost_sites

      JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id, ost_clients.client_id

那会(基本上)返回

SITE     CLIENT  
2        A
3        B
3        C
4        D
4        E
4        F 

查询三

      SELECT 
          ost_sites.site_id as SITE,   
          ost_clients.client_id as CLIENT

      FROM ost_sites

      FULL OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      FULL OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id, ost_clients.client_id

那会(基本上)返回

SITE     CLIENT  
1        NULL
2        A
3        B
3        C
4        D
4        E
4        F
5        NULL
NULL     G
NULL     H 

查询四

      SELECT DISTINCT ost_sites.site_id as SITE 

      FROM ost_sites

      LEFT OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      LEFT OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      ORDER BY ost_sites.site_id 
      ORDER BY ost_sites.site_id 

那会(基本上)返回

SITE         
2
3
4 

查询五

      SELECT 
          ost_sites.site_id as SITE,
          count(ost_clients.client_id) as CLIENT_COUNT

      FROM ost_sites

      JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      GROUP BY ost_sites.site_id 
      ORDER BY ost_sites.site_id 

那会(基本上)返回

SITE        CLIENT_COUNT  
2           1
3           2
4           3

查询五

      SELECT 
          ost_sites.site_id as SITE,
          count(ost_clients.client_id) as CLIENT_COUNT

      FROM ost_sites

      LEFT OUTER JOIN ost_site_auth 
          ON ost_sites.site_id=ost_site_auth.site_id 

      LEFT OUTER JOIN ost_clients
          ON ost_site_auth.client_id=ost_clients.client_id

      GROUP BY ost_sites.site_id 
      ORDER BY ost_sites.site_id 

那会(基本上)返回

SITE        CLIENT_COUNT  
1           0
2           1
3           2
4           3
5           0