Need help - query multiple tables for data
Posted: Thu Aug 17, 2006 3:17 pm
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
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