mysql - 如何使用 GROUP_CONCAT() 排除没有相关行的连接表

Question

当 3 个表(表 A、表 B 和表 C)中的每一个都存在一行或多行时，下面所示的查询将起作用并返回所有行。

但是，当连接的表之一(特别是我的情况下的表 C)没有行时，查询将失败并且不返回任何内容。

我需要下面的查询才能在表 B 或表 C 可能没有任何行的情况下工作。

$query_string = '
  SELECT tableA.*,
    GROUP_CONCAT(DISTINCT tableB.tbID ORDER BY tableB.sortOrder) AS tbID,
    GROUP_CONCAT(DISTINCT tableC.tcID ORDER BY tableC.sortOrder) AS tcID,
    tableD1.fullName AS createdByFullName,
    tableD2.fullName AS lastUpdatedByFullName
  FROM table_A AS tableA
  INNER JOIN table_B AS tableB ON tableA.ID = tableB.ID
  INNER JOIN table_C AS tableC ON tableA.ID = tableC.ID
  LEFT JOIN table_D AS tableD1 ON tableD1.ID = tableA.createdBy
  LEFT JOIN table_D AS tableD2 ON tableD2.ID = tableA.lastUpdatedBy
  WHERE tableA.ID = "' . $this_ID . '"
  GROUP BY tableB.ID, tableC.ID
  LIMIT 1
';

这是表格

table_A
+----+------+------+-----------+---------------+
| ID | col1 | col2 | createdBy | lastUpdatedBy |
+----+------+------+-----------+---------------+
| 01 | data | data |    02     |      01       |
| 02 | data | data |    03     |      02       |
| ...                                          |
| 99 | data | data |    01     |      02       |
+----+------+------+-----------+---------------+

table_B
+----+------+-----------+
| ID | tbID | sortOrder |
+----+------+-----------+
| 01 |  01  |     2     |
| 01 |  02  |     1     |
| 02 |  01  |     2     |
| 02 |  02  |     3     |
| 02 |  03  |     1     |
| 99 |  01  |     1     |
+----+------+-----------+

table_C (query works when $this_ID="02" exists)
+----+------+-----------+
| ID | tcID | sortOrder |
+----+------+-----------+
| 01 |  01  |     1     |
| 02 |  01  |     2     |
| 02 |  02  |     1     |
| 99 |  01  |     1     |
+----+------+-----------+

table_C (query does not work when $this_ID="02" does not exist)
+----+------+-----------+
| ID | tcID | sortOrder |
+----+------+-----------+
| 01 |  01  |     1     |
| 99 |  01  |     1     |
+----+------+-----------+

table_D (this table is not relevant to the example, but exists in my query)
+----+------------+
| ID | fullName   |
+----+------------+
| 01 | John Doe   |
| 02 | Mary Jones |
| 03 | Joe Smith  |
+----+------------+

这是我尝试过的方法(均未成功):

T&E #1

GROUP_CONCAT(DISTINCT tableB.tbID ORDER BY tableB.sortOrder) AS tbID,
GROUP_CONCAT(DISTINCT tableC.tcID ORDER BY tableC.sortOrder WHERE tableC.ID <> "") AS tcID,

T&E #2

WHERE tableA.ID = "' . $this_ID . '" AND tableC.ID <> ""

注释:我专门测试表 C，但如果表 B 中也没有相关行，我需要相同的解决方案才能工作。谢谢。

Answer 1

一个简单的LEFT JOIN应该可以解决这个问题。

即使表C中不存在，这也会给出表A的结果，但如果存在则显示它们。这要求表 B 中存在数据关系。

将其更改为 LEFT JOIN(如果情况不应该如此)。

  SELECT tableA.*,
  GROUP_CONCAT(DISTINCT tableB.tbID ORDER BY tableB.sortOrder) AS tbID,
  GROUP_CONCAT(DISTINCT tableC.tcID ORDER BY tableC.sortOrder) AS tcID,
  tableD1.fullName AS createdByFullName,
  tableD2.fullName AS lastUpdatedByFullName
  FROM table_A AS tableA
  INNER JOIN table_B AS tableB ON tableA.ID = tableB.ID
  LEFT JOIN table_C AS tableC ON tableA.ID = tableC.ID
  LEFT JOIN table_D AS tableD1 ON tableD1.ID = tableA.createdBy
  LEFT JOIN table_D AS tableD2 ON tableD2.ID = tableA.lastUpdatedBy
  WHERE tableA.ID = "' . $this_ID . '"
  GROUP BY tableB.ID, tableC.ID
  LIMIT 1