MySQL - How can you select multiple columns on a nested IFNULL...GROUP_CONCAT() condition?

Question

I have a web application which is connected to a MySQL (5.5.64-MariaDB) database.

One of the queries is as follows:

SELECT 
        d.id, 
        d.label AS display_label, 
        d.anchor, 
        r.id AS regulation_id, 
        IFNULL(
            (SELECT GROUP_CONCAT(value) FROM display_substances `ds` 
              WHERE `ds`.`display_id` = `d`.`id`
              AND ds.substance_id = 1 -- For example, substance ID = 1 
              GROUP BY `ds`.`display_id`
            ), "Not Listed"
        ) `display_value` FROM displays `d`
        JOIN groups g ON d.group_id = g.id
        JOIN regulations r ON g.regulation_id = r.id

An example of the output is as follows:

+-----+------------------------------------+------------------------------------------------------------------------------------------+
| id  | name                               | display_value                                                                            |
+-----+------------------------------------+------------------------------------------------------------------------------------------+
|   4 | techfunction                       | Intermediate / monomer;  Corrosion inhibitor / anodiser / galvaniser; Catalyst; Additive |
| 323 | russia_chemsafety_register_display | Not Listed                                                                               |
| 733 | peru_pcb_display                   | Not Listed                                                                               |
+-----+------------------------------------+------------------------------------------------------------------------------------------+

This query does what we need. For explanatory purposes:

There are 2 tables, displays and display_substances
The query is obtaining display_substances.value for each displays.id
If there is no corresponding display_substances.value then the string "Not Listed" (refer to query above) is returned. If there is a corresponding value then display_substances.value is returned. So in the example data above, IDs 323 and 733 refer to a scenario where there is no corresponding entry, therefore we want "Not Listed". Conversely ID 4 does have a value ("Intermediate / monomer; Corrosion inhibitor / anodiser / galvaniser; Catalyst; Additive") so we get that.

The table structures are as follows:

DESCRIBE displays;
+----------+----------------------+------+-----+---------+----------------+
| Field    | Type                 | Null | Key | Default | Extra          |
+----------+----------------------+------+-----+---------+----------------+
| id       | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(127)         | NO   |     | NULL    |                |
| label    | varchar(255)         | NO   |     | NULL    |                |
+----------+----------------------+------+-----+---------+----------------+


DESCRIBE display_substances;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| display_id   | smallint(5) unsigned  | NO   | MUL | NULL    |                |
| substance_id | mediumint(8) unsigned | NO   | MUL | NULL    |                |
| value        | text                  | NO   |     | NULL    |                |
| automated    | tinyint(4)            | YES  |     | NULL    |                | 
+--------------+-----------------------+------+-----+---------+----------------+

I want to be able to return display_substances.automated (refer to table structure above) as a column from my query. But I can't see how to do this.

The reference to the display_substances table is ds, so I cannot use that in the initial SELECT statement because at that point there's no alias. Equally there is no JOIN condition that would make it possible, because not every row returned obtains data from display_substances (i.e. those that are "Not Listed" are not getting anything from that table).

If I want an additional column next to display_value in the sample output above that shows display_substances.automated, or NULL if it doesn't exist, how can I achieve that?

For reference the automated field either contains a 1 (to represent data that has been obtained through automated processes by our application), or NULL if it isn't automated.

user_10498828 · Answer

there is no JOIN condition that would make it possible, because not every row returned obtains data from display_substances

For this case you can use a LEFT JOIN:

SELECT d.id, d.label display_label, d.anchor, r.id regulation_id, 
  COALESCE(ds.value, 'Not Listed') display_value,
  ds.automated     
FROM displays d
INNER JOIN groups g ON d.group_id = g.id
INNER JOIN regulations r ON g.regulation_id = r.id
LEFT JOIN (
  SELECT display_id, GROUP_CONCAT(value) value, MAX(automated) automated     
  FROM display_substances  
  WHERE substance_id = 1
  GROUP BY display_id
) ds ON ds.display_id = d.id

I used MAX(automated) as the returned column, but you can use GROUP_CONCAT(automated) just like you do for value and also COALESCE():

COALESCE(ds.automated, 'Not Listed')

MySQL - How can you select multiple columns on a nested IFNULL...GROUP_CONCAT() condition?

1 Answers