tables, joins or not, to script or not
Is there any way to use a join or join-like (union or subquery) command to take the results of one query as constraints for another and produce a single results table, or must I use a scripting language like PHP to stitch this together?
In the following example, I am trying to find all the ingredients (in groups identified by a field called "clusterid") considered at a given meeting (defined as event number 444).
For example:
SELECT clusterid
FROM assessment
WHERE eid=444
yields a set of 13 numbers that identify 13 sets of numbers representing the ingredients themselves.
So a chart with the following values is returned: 13, 21, 29, 32, 34, 35, 36, 42, 47, 48, 49, 50, 61. There is only 1 ingredient in clusterid 13, but clusterid 50 has 78 ingredients in a column called MonoID.
[What if any description of the assessment table is needed????? The assessment table tracks the name of the clusters in the clusterid column (with several other columns) & the cluster table has the clusterids and the MonoID fields (only) which is used to retrieve the more human-eye-friendly details of each ingredient.]
The desired results table would have as columns identifying information about each ingredient in each clusterid, and as many rows as there are unique ingredients.
SELECT track.cluster.MonoID, x.y.MonoID, x.y.a, x.y.b, x.y.c, x.y.d
FROM track.cluster
INNER JOIN x.y
ON x.y.MonoID = track.cluster.MonoID
WHERE clusterid = 13
OR .... 21
OR ... 29
etc,
yields 111 result rows (which for the purposes of this question is just about right).
To restate: Is there a way without using PHP to avoid having to manually construct these queries separately and manually plugin results of one to the other?
I ask not because I do not want to use PHP (an inevitability). I ask because I want to see if this question suggests more fundamental problems in the organization of the data and whether I'm missing something fundamental about what MySQL can do through phpmyadmin (likely groan, I know :). I am not sure whether or not I/you can assume the assessment table is best normalized for this question......hopefully this question makes some sense and is posted correctly! All help, even a WTHeck, is appreciated.
Thanks,
Kevin Fries