[RESOLVED] Need help with a complex data query (like a crosstab but even a bit worse).

Joseph_Sliker

Hi; I am having trouble conceptualizing and constructing a complex mysql query.

Say you are testing a kid on an instrument that has several items (the answer string is stored for each item).
Then, someone else tests the same kid the same day with a parallel test for a reliability study.

I've got a PROTOCOL table with a primary key "ID" that contains columns for the kid's name, the tester's name, test date, etc..
protocolID|Kidname|Date|Tester|

I've also got a DATA table to hold the answer for each item (it contains the ID to relate it back to the PROTOCOL table.
ie. the tables stucture is like so:
pri_data_id|protocolID|item|answer|

I know how to conceptualize and construct a query that will give me, for each kid, a single row that contains a column for each of the PROTOCOL fields AND a column for each test item so that I end up with all the data for a single test session in a single row.

However, I want to contstruct a query that ALSO provides me with the SECOND tester's data on the SAME row.

Get it?

I DID construct a third table: YOKEDDATA, thinking that might help, that contains the protocol ID pair ...so that is available to help put the protcols together
pri_yokeID|protocol_ID_a|protocol_ID_b|

Are there any MySql gurus out there who can help me with this?

My understanding of MySql queries is pretty basic, (ie SELECT/FROM/WHERE) but I have had some luck rotely applying the MySql Wizardry document about creating pseudo crosstab-queries from the MySql documentation site. But I think I'm out of my depth for this one.

Thanks!

johanafm

I don't get why you'd want to have columns item1, item2, item3 etc. It makes more sense to have one row per answer. Also, the kids name in protocol ought to be a kid id, relating to a kid table which contains the name. Otherwise, you don't have a way to diffrentiate between two Kevins. The same goes for the testers. Otherwise two Bobs working as testers would be indistinguishable from each other. Wether you put kids and testers in a person table with a type field identifying some as kids and some as testers or use separate tables is up to you.

SELECT tester.name as testername, item, answer
FROM protocol
INNER JOIN data ON protocol.protocolID = data.protocolID
INNER JOIN tester ON protocol.tester_id = tester.id
WHERE protokol.kid_id = 14
ORDER BY testername ASC, item ASC

14 is just an example of course. But replacing that with the id of a selected child should be no problem. Or if you want to show results for all kids leave the WHERE clause out altogether and also select kid_id and kid_name (join in table kid) and order by kids name asc, kids id asc, testername asc, item asc.

Joseph_Sliker

Thanks for the reply. Actually, my tables are a bit more sophisticated than described I just wanted to keep the description as simple as possible to save me some typing. Yes, I am dealing with unique student IDs and all that good stuff.

I'm trying to build the query output as described in order to export it for statistical analysis by others: just trying to save them the work of/potential errors in re-arranging the data output for whatever stat packages they might use. That's why I want all the data that comes from a pair of "yoked" test sessions to be on a single row.

I'm not real clear on the use of INNER joins. I will look over your code and see if I can apply it to my problem.

Joseph_Sliker

The code proposed did not seem to work (or I got confused changing the tables and fieldnames into my actual tables and fieldnames). I came up with something that DOES seem to ALMOST produce exactly the result I'm shooting for, but not quite.

{
I believe my error is in the construction of the answer columns, i.e.
GROUP_CONCAT(if(DATA_A.SRV_ITEMID='1008', DATA_A.ANSWER, NULL)) AS 'A_1',
}

...the above construction outputs umpteen copies of the answer value in each answer column (because of the group_concat syntax) I only want the answer string once

But the following is real close...

SELECT SRV_YOKEDDATA.*,

PROTS_A.RESPONDER AS TESTER_A,
GROUP_CONCAT(if(DATA_A.SRV_ITEMID='1008', DATA_A.ANSWER, NULL)) AS 'A_1',
GROUP_CONCAT(if(DATA_A.SRV_ITEMID='1009', DATA_A.ANSWER, NULL)) AS 'A_2',
GROUP_CONCAT(if(DATA_A.SRV_ITEMID='1010', DATA_A.ANSWER, NULL)) AS 'A_3',

PROTS_B.RESPONDER AS TESTER_B,
GROUP_CONCAT(if(DATA_B.SRV_ITEMID='1008', DATA_B.ANSWER, NULL)) AS 'B_1',
GROUP_CONCAT(if(DATA_B.SRV_ITEMID='1009', DATA_B.ANSWER, NULL)) AS 'B_2',
GROUP_CONCAT(if(DATA_B.SRV_ITEMID='1010', DATA_B.ANSWER, NULL)) AS 'B_3'

FROM SRV_YOKEDDATA

LEFT JOIN SRV_PROTOCOLS AS PROTS_A ON SRV_YOKEDDATA.SRV_PROTOCOLID_A=PROTS_A.SRV_PROTOCOLID
LEFT JOIN SRV_PROTOCOLS AS PROTS_B ON SRV_YOKEDDATA.SRV_PROTOCOLID_B=PROTS_B.SRV_PROTOCOLID
LEFT JOIN SRV_DATA AS DATA_A ON SRV_YOKEDDATA.SRV_PROTOCOLID_A=DATA_A.SRV_PROTOCOLID
LEFT JOIN SRV_DATA AS DATA_B ON SRV_YOKEDDATA.SRV_PROTOCOLID_B=DATA_B.SRV_PROTOCOLID

GROUP BY SRV_YOKEID

It seems that it is the construction of the answer columns (ie A_1, A_2... B_1, B2...) that is the problem...

Joseph_Sliker

DISTINCT seems to fix the problem, now I just have to figure out why some columns have a comma in them when no answer was recorded for the item

GROUP_CONCAT(DISTINCT if(DATA_A.SRV_ITEMID='1008', DATA_A.ANSWER, NULL)) AS 'A_1',

| SRV_YOKEID | SRV_PROTOCOLID_A | SRV_PROTOCOLID_B | EXTR_COMMENT | WALKER_A | A_1 | A_2 | A_3 | A_4 | A_5 | A_6 | A_7a | A_7b | A_7c | A_8a | A_8b | WALKER_B | B_1 | B_2 | B_3 | B_4 | B_5 | B_6 | B_7a | B_7b | B_7c | B_8a | B_8b |
+------------+------------------+------------------+--------------+----------+---------------+------+-------+------------+------+------+------+------+------+------+-------------+----------+---------------+------+-------+----------+------+------+------+------+------+------+-------------+
| 1 | 15248 | 15249 | NULL | slikerjm | Yes | ALL | IND | CREATE | YES | YES | Yes | Yes | , | Yes | LITTLE | appssm | Yes | MOST | GRP | EVALUATE | YES | NO | , | Yes | Yes | , | LITTLE |
| 2 | 15256 | 15257 | NULL | slikerjm | Yes | ALL | IND | CREATE | YES | NO | Yes | , | , | Yes | LITTLE | appssm | Yes | MOST | GRP | EVALUATE | NO | YES | Yes | , | , | Yes | LITTLE |

Joseph_Sliker

if I construct my data columns like so...

GROUP_CONCAT(DISTINCT if(DATA_A.SRV_ITEMID='1008', DATA_A.ANSWER, NULL) SEPARATOR '') AS 'A_1',

(Note the addition of [SEPARATOR ''] to the end of the GROUP_CONCAT() construction) it gets rid of that pesky comma in the resulting data field.

I am now where I need to be with this.