Hello,
I have two tables--QUESTIONS, ANSWERS. I store the questions in one table and the answers in the second.
Table Structure--QUESTIONS
Field 1: Question_ID
Field 2: Question
Field 3: Question_Type (There are 3 types of questions)
Field 4: Step_ID (There are 15 steps in the program)
Table Structure--ANSWERS
Field 1: Question_ID
Field 2: User_ID
Field 3: Answer
The Following query returns the correct data for one user with the questions in column 1 and the answers in column 2--even if the user has not yet answered the question.
SELECT questions.question_id, questions.question, questions.question_type, answers.answer
FROM answers
RIGHT JOIN questions ON answers.question_id = questions.question_id
AND answers.user_id = '357437'
ORDER BY questions.step_id, questions.question_id
What I would now like to achieve is output the the answers for user '357437' in column 2 as above, but then output the answers for user '457438' in column 3, user '557439' in column 4, etc. such as this:
Question_ID, Question, Question_Type, answer-user1, answer-user2, answer-user3, answer-user4, etc.
I was thinking that I needed to construct a crosstab query, but that type of query seems to be designed soley to do calculations. I've tried searching for an example of what I want to achieve in various MySQL forums--with no results. Is what I want to do possible in MySQL with the tables constructed as above? Can anyone point me in the right direction.