Page 1 of 1

Need help - query multiple tables for data

Posted: Thu Aug 17, 2006 3:17 pm
by Sinemacula
I'm not very good at working out complex queries, and I'm trying to extract data from a system designed by someone else that uses multiple tables to store the data I need.

The tables with data that I need (and those that link between tables with data that I need), and their columns are:

qst_questionnaires
qst_questionnaires.questionnaire_ID
qst_questionnaires.questionnaire_name

qst_stored_questionnaires
qst_stored_questionnaires.stored_questionnaire_ID
qst_stored_questionnaires.stored_questionnaire_date
qst_stored_questionnaires.questionnaire_ID
qst_stored_questionnaires.user_ID

qst_stored_answers
qst_stored_answers.stored_answer_ID
qst_stored_answers.stored_questionnaire_ID
qst_stored_answers.q2q_ID
qst_stored_answers.answer_ID
qst_stored_answers.answer_text

*if answer_ID has a value, then answer_text is NULL and vice versa

qst_answers
qst_answers.answer_ID
qst_answers.answer_name
qst_answers.answer_value

qst_questions
qst_questions.question_ID
qst_questions.question_name
qst_questions.question_type

qst_questions_to_answers
qst_questions_to_answers.q2a_ID
qst_questions_to_answers.question_ID
qst_questions_to_answers.answer_ID
qst_questions_to_answers.ordering
qst_questions_to_answers.default.value

qst_questionnaires_to_questions
qst_questionnaires_to_questions.q2q_ID
qst_questionnaires_to_questions.questionnaire_ID
qst_questionnaires_to_questions.question_ID
qst_questionnaires_to_questions.question_group_ID

qst_question_groups
qst_question_groups.question_group_ID
qst_question_groups.question_group_name

mos_users
mos_users.id (maps to qst_stored_questionnaires.user_ID)
mos_users.name

What I want to do is query based on qst_questionnaires.questionnaire_ID to produce output that includes the following columns, ordered by qst_stored_questionnaires.stored_questionnaire_ID:

qst_stored_questionnaires.stored_questionnaire_ID
qst_questionnaires.questionnaire_name
qst_questions_groups.question_group_ID
qst_question_groups.question_group_name
qst_answers.answer_name*
qst_answers.answer_value*
qst_stored_answers.answer_text*
mos_users.name

*qst_stored_answers.answer_ID, which links to qst_answers.answer_name and qst_answers.answer_value will be NULL if there is a value for qst_stored_answers.answer_text

I'm not even sure where to start...

Would anyone be willing to help me out with a query that will do this?

Thanks,
Scott

Posted: Thu Aug 17, 2006 9:56 pm
by feyd
Your choice of table structure is rather confusing so this may be completely wrong.

Code: Select all

SELECT
   `qst_stored_questionnaires`.`stored_questionnaire_ID`,
   `qst_questionnaires`.`questionnaire_name`
   `qst_questions_groups`.`question_group_ID`,
   `qst_questions_groups`.`question_group_name`,
   `qst_answers`.`answer_name`
   `qst_answers`.`answer_value`
   `qst_stored_answers`.`answer_text`
   `mos_users`.`name`
FROM `qst_questionaires`
INNER JOIN `qst_questionnaires_to_questions`
   ON( `qst_questionnaires_to_questions`.`questionnaire_id` = `qst_questionnaires`.`questionnaire_id` )
INNER JOIN `qst_question_groups`
   ON( `qst_question_groups`.`question_group_id` = `qst_questionnaires_to_questions`.`question_group_id` )
INNER JOIN `qst_questions`
   ON( `qst_questions`.`question_ID` = `qst_questionnaires_to_questions`.`question_ID` )
INNER JOIN `qst_questions_to_answers`
   ON( `qst_questions_to_answers`.`question_ID` = `qst_questions`.`question_ID` )
INNER JOIN `qst_answers`
   ON( `qst_answers`.`answer_ID` = `qst_questions_to_answers`.`answer_ID` )
LEFT JOIN `qst_stored_questionnaires`
   ON( `qst_stored_questionnaires`.`questionnaire_id` = `qst_questionaires`.`questionnaire_id` )
INNER JOIN `qst_stored_answers`
   ON( `qst_stored_answers`.`stored_questionnaire_id` = `qst_stored_questionnaires`.`stored_questionnaire_id` )
INNER JOIN `mos_users`
   ON( `mos_users`.`id` = `qst_stored_questionnaires`.`user_id` )
WHERE
   `qst_questionnaires`.`questionnaire_ID` = '123'

Posted: Fri Aug 18, 2006 12:34 am
by Sinemacula
Thanks feyd!

Yeah, I didn't create the table structure myself, so I'm just working with what I've got.

Your solution does appear to get all the fields I want, but ends up returning every possible field value, instead of just what was actually answered.

Here's something I found digging around some old files that gets close:

Code: Select all

SELECT
u.name AS `user`, sa.stored_questionnaire_ID AS `test administration #`, ifnull(sa.stored_answer_text, a.answer_value) as `answer value`, ifnull(sa.stored_answer_text, a.answer_name) as `answer name`, qg.question_group_name AS `subscale`, q2q.question_ID AS `question id`
            FROM 
              qst_stored_questionnaires AS sq 
              JOIN qst_stored_answers AS sa USING(stored_questionnaire_ID) 
              JOIN qst_questionnaires_to_questions AS q2q USING(q2q_ID)
              JOIN qst_questions AS q USING(question_ID)
JOIN qst_question_groups_to_questionnaires AS qg2q ON(q2q.question_group_ID = qg2q.qg2q_ID)
JOIN qst_question_groups AS qg ON(qg2q.question_group_ID = qg.question_group_ID)
JOIN mos_users AS u ON(sq.user_ID = u.id)
              LEFT JOIN qst_answers AS a ON(sa.answer_ID = a.answer_ID)
            WHERE 
                sq.questionnaire_ID = 4 ORDER BY sa.stored_questionnaire_ID
Only it doesn't get all the fields I was hoping for - it's missing the qst_question_groups.question_group_ID and the qst_questionnaires_name fields... but I may be able to figure out how to add those once I get some sleep. :D

Thanks,
Scott