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:
-
substances
- a list of chemical substances, all of which have a unique ID (substances.id
) -
ecs
- EC Number* values. -
cas
- CAS Number* values. -
ecs_substances
- Mappingsubstances.id
toecs.id
. Onesubstances.id
may be mapped to one, or more than oneecs.id
-
cas_substances
- Mappingsubstances.id
tocas.id
. Onesubstances.id
may be mapped to one, or more than onecas.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 "
" XYZ-876
I understand that the logic to be as follows:
SELECT * FROM substances
-
SELECT * FROM cas_substances WHERE cas_substances.substance_id =
Result(s) from 1 (substances.id
) -
SELECT cas.value FROM cas WHERE cas.cas.id =
Result(s) from 2 (cas_substances.cas_id
) - 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).