0

I don't know if this is possible but I'm trying to use the value from a MySQL SELECT query in multiple subqueries following the advice given at How to specify the parent query field from within a subquery in mySQL?. There are unequal numbers of results in each subquery, which I suspect is why I can't get this working.

My DB is more complex than on the above post. I'm dealing with 5 tables which are as follows:

  1. substances - a list of chemical substances, all of which have a unique ID (substances.id)
  2. ecs - EC Number* values.
  3. cas - CAS Number* values.
  4. ecs_substances - Mapping substances.id to ecs.id. One substances.id may be mapped to one, or more than one ecs.id
  5. cas_substances - Mapping substances.id to cas.id. One substances.id may be mapped to one, or more than one cas.id

* These are terms used for classifying chemicals.

An example of how the data may look for 1 substance:

substances.id  | substances.name
-------------- | ----------------
1              | FooBar

An example of the assignment to multiple CAS numbers (the same principle applies for EC but am just using CAS for brevity):

cas_substances.id | substance_id
--------------------------------
997               | 1
----------------- | ------------
23423             | 1
--------------------------------

And then the CAS Numbers themselves:

cas.id | cas.value
------ | ---------
997    | ABC-123
------ | ---------
23423  | XYZ-876
------------------

The desired result is that I want to obtain a list of each substances.id, substances.name with a corresponding list of the CAS/EC Numbers, ideally formatted with a line break, e.g.

substances.id  | substances.name | cas_values         |
-------------- | --------------- | ------------------ |
1              | FooBar          | ABC-123 "\n" XYZ-876

I understand that the logic to be as follows:

  1. SELECT * FROM substances
  2. SELECT * FROM cas_substances WHERE cas_substances.substance_id = Result(s) from 1 (substances.id)
  3. SELECT cas.value FROM cas WHERE cas.cas.id = Result(s) from 2 (cas_substances.cas_id)
  4. Formatting to handle the fact there may (or may not) be multiple cas_substances rows - line break between records if there is.

I don't understand how to write the SQL that gets Results from 1/2.

Is it even possible to do this in MySQL? The application I'm building is written in PHP but I figured using SQL only will be far quicker than doing multiple subqueries in PHP (although it is much easier to write in PHP).

Andy
  • 5,142
  • 11
  • 58
  • 131

2 Answers2

1

You can join these three tables together, then use group by and group_concat to achieve that.

select
    `sub`.*,
    group_concat(cas.`value` separator ' "\n" ') as cas_values
from substances `sub`
left join cas_substances cs on `sub`.id = cs.substance_id
left join cas on cas.id = cs.cas_id
group by `sub`.id

A demo here.

Note: GROUP_CONCAT only works in mysql.

Blank
  • 12,308
  • 1
  • 14
  • 32
  • Thank you so much, this works. Is it possible to give an alias to `group_concat()` because in the returned data the column name is `group_concat(cas.`value` separator ' "\n" ') `. I also need to do the same for EC Number, so could do with the cols as `cas_values` and `ec_values` to make them easy to refer to for later on. – Andy Jun 26 '17 at 10:07
  • @Andy Alias is possible. – Blank Jun 26 '17 at 10:10
0

The accepted answer works but I have expanded this to include all 5 tables:

select
`sub`.*,
group_concat(cas.`value` separator ' "\n" ') as cas_values,
group_concat(ecs.`value` separator ' "\n" ') as ecs_values
from substances `sub`
left join cas_substances cs on `sub`.id = cs.substance_id
left join cas on cas.id = cs.cas_id
left join ecs_substances ec on `sub`.id = ec.substance_id
left join ecs on ecs.id = ec.ec_id
group by `sub`.id
Andy
  • 5,142
  • 11
  • 58
  • 131