Complex MySQL queries: A little help please?

Joseph_Sliker

Hi;

With many thanks to people in this forum who have helped me a LOT, I have built a functional test data entry and graphing system. My users enter test scores for kids. The dated scores all wind up in the same table along with various other related data like student ID, testdate, testphase (more convenient than raw dates for grouping) etc. etc.

One of the graphs looks at the same table twice (TableName and TableName2 both refer to the same table) to get Fall and Winter testphase scores for a comparison graph (using the extremely cool jpgraph)

here is an example:
example

The following query works perfectly:

$Query = "SELECT $TableName2.SCHOOLID AS SCHOOL, $TableName2.GRADE AS GD, $TableName.ROOM AS RM, $TableName2.FIRST AS FIRST, $TableName2.LAST AS LAST, $TableName.TESTPHASE, $TableName.ORF_MWRC AS WRC1, $TableName2.TESTPHASE, $TableName2.ORF_MWRC AS WRC2, $TableName2.LANG AS LNG FROM $TableName AS $TableName2 LEFT JOIN $TableName ON $TableName2.MPSID = $TableName.MPSID WHERE (($TableName2.SCHOOLID='$SCHOOLID') AND ($TableName2.GRADE='$GRADE') AND ($TableName2.LANG='$LANG') AND ($TableName2.TESTPHASE='$TESTPHASE_A')) AND (($TableName2.SCHOOLID='$SCHOOLID') AND ($TableName2.GRADE='$GRADE') AND ($TableName.ROOM='$ROOM') AND ($TableName2.LANG='$LANG') AND ($TableName.TESTPHASE='$TESTPHASE_B')) ORDER BY $TableName.ORF_MWRC";

The problem I am having is that the TESTPHASE value in the record is a code (ie. FS="Fall Screening", WS="Winter Screening" etc. I built a table called PHASECODES as a lookup table that has only two fields: TESTPHASE (the code), and PHASENAME (the longer text string).

I'm sure you know where I'm going with this: I would like PHASENAME to end up in my Result set so I can use it as part of the output for the resulting graphs. It's pretty easy to do this in MS Access with nested queries, but I'm not quite sure what this would look like in MySQL/php.

Anybody got a clue?

craigh

Originally posted by Joseph Sliker
$Query = "SELECT $TableName2.SCHOOLID AS SCHOOL, $TableName2.GRADE AS GD, $TableName.ROOM AS RM, $TableName2.FIRST AS FIRST, $TableName2.LAST AS LAST, $TableName.TESTPHASE, $TableName.ORF_MWRC AS WRC1, $TableName2.TESTPHASE, $TableName2.ORF_MWRC AS WRC2, $TableName2.LANG AS LNG FROM $TableName AS $TableName2 LEFT JOIN $TableName ON $TableName2.MPSID = $TableName.MPSID WHERE (($TableName2.SCHOOLID='$SCHOOLID') AND ($TableName2.GRADE='$GRADE') AND ($TableName2.LANG='$LANG') AND ($TableName2.TESTPHASE='$TESTPHASE_A')) AND (($TableName2.SCHOOLID='$SCHOOLID') AND ($TableName2.GRADE='$GRADE') AND ($TableName.ROOM='$ROOM') AND ($TableName2.LANG='$LANG') AND ($TableName.TESTPHASE='$TESTPHASE_B')) ORDER BY $TableName.ORF_MWRC";
The problem I am having is that the TESTPHASE value in the record is a code (ie. FS="Fall Screening", WS="Winter Screening" etc. I built a table called PHASECODES as a lookup table that has only two fields: TESTPHASE (the code), and PHASENAME (the longer text string).

I'm sure you know where I'm going with this: I would like PHASENAME to end up in my Result set so I can use it as part of the output for the resulting graphs. It's pretty easy to do this in MS Access with nested queries, but I'm not quite sure what this would look like in MySQL/php.

Anybody got a clue? [/B]

So - you need another left join...

select * from table left join table2 on.... left join PHASECODES ON othertable.TESTPHASE=PHASECODES.PHASENAME

add that bit in there (sorry I didn't use your actual table names, my browser won't display your stuff very well, so it is hard to read) and it should work.