SQL Join

[smti]

Hi Everyone:

 

The Situation:

 

I am working on a web-based questionnaire project. I have two tables: WizardQuestions and WizardChoices -- WizardQuestions stores the questions themselves, while the WizardChoices stores the possible answers associated with each question.

 

Database Schema:

 

WizardQuestions has the following fields:

1. question_id

2. question_header

3. question_body

 

WizardChoices has the following fields:

1. choice_id

2. assoc_question_id (Relates to table above)

3. choice_text

 

 

The Problem:

Using one query, I need to get:

 

1. question_header from WizardQuestions

2. question_body from WizardQuestions

3. choice_text from WizardChoices associated with that question.

 

** Header and question body should be returned once **

 

I have tried:

 

The inter-select:

Select wizard_questions.question_id, 
wizard_questions.question_header, 
wizard_questions.question_body
FROM 
wizard_questions
WHERE 
wizard_questions.question_id = 
(Select wizard_question_choices.choice_text from wizard_question_choices where wizard_questions.question_id = wizard_question_choices.assoc_question_id);

 

The JOIN:

SELECT WizardQuestions.question_header,
WizardQuestions.question_body,
WizardChoices.choice_text
FROM WizardQuestions
JOIN WizardChoices
ON WizardQuestions.question_id = WizardChoices.assoc_question_id;

 

I have had very little success. If I run the join statement without a GROUP BY statement, then the question_header and question_body are shown twice -- with both choice options, but if I add a GROUP BY statement, then the header and body are shown, but with one option only.

 

Any help would be greatly appreciated!

 

 

[requinix]

Can't do it as you've stated. You have to give up one of the requirements. Either

a) Use multiple queries (one for the question, one for the choices of each question). This is bad so don't do it.

b) Allow the question information to be repeated in the results. Have the code deal with it.

c) Another option which I won't mention because it's even worse than (a).

[Barand]

You could try GROUP_CONCAT

SELECT WizardQuestions.question_header,
WizardQuestions.question_body,
GROUP_CONCAT(WizardChoices.choice_text) as choices
FROM WizardQuestions
JOIN WizardChoicesON WizardQuestions.question_id = WizardChoices.assoc_question_id
GROUP BY WizardQuestions.question_id 

[awjudd]

I believe that technically you could use a variable and a case statement but that isn't what the database is made for ... so it is a bad idea!

 

~awjudd