tables, joins or not, to script or not
Posted by: Kevin Fries
Date: March 19, 2010 07:03AM

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
ON x.y.MonoID = track.cluster.MonoID
WHERE clusterid = 13
OR .... 21
OR ... 29

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.

Kevin Fries

Options: ReplyQuote

Written By
tables, joins or not, to script or not
March 19, 2010 07:03AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.