How To Exclude Joined Table with no Related Rows using GROUP_CONCAT()

Question

When one or more rows exist in each of 3 tables (table A, table B, and table C) the query shown below works and returns all rows.

However, when one of the joined tables (specifically table C in my case) has no rows, the query fails and returns nothing.

I need the query below to work in cases where table B or table C may not have any rows.

$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
';

Here are the Tables

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  |
+----+------------+

Here's what I have tried (each having no success):

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 <> ""

Notes: I am specifically testing Table C but I need the same solution to work if there are no related rows in Table B as well. Thanks.

Answer 1

A simple LEFT JOIN should fix this.

This will give the result from table A even if they dont exists in table C, but will display them if they do. This requiers tho that data relation exists in table B.

Change that to a LEFT JOIN also if that shouldnt be the case.

  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