Sunday, April 27, 2008

Need help with select join query string (3 replies)

I am trying to write a question and answer script using PHP/MYSQL and need help on getting the select query to work the way I want it.

I have three tables, one for the question, one for the answer choices, and one that references which answer goes with which question. There are 4 choices for each question (although some may only have 2)

i.e.

This is a question
Answer A
Answer B
Answer C
Answer D

I have written a select statement, but I am getting the question 4 times.

This is a question Answer A
This is a question Answer B
This is a question Answer C
This is a question Answer D

How could I change this select statement so I only get the question once and then the four answers - is this even possible using one select statement?

Here is the code I used:

select answers.answers, questions from questions join reference join answers where reference.idq=questions.idq and reference.ida=answers.ida;

Thanks