Possible Join Help ?

[Johneh]

Not really sure if Join is the correct way to go about this, but here goes anyways:

 

MySQL Version is: 5.1.22-rc

 

We did consider using UNION but still couldn't get that to work.

 

We have 2 tables,

 

Questions

question_id
question_text

 

Answers

answer_id
question_id
user_id
answer_text

 

There are a finite amount of questions, but they are subject to change. There are multiple rows within the answers table that links to the relevant question, each answer is also linked to a user.

 

We're trying to make a view that looks something like this:

user_idQuestion 1Question 2etc..

1Answer 1Answer 2[/td]

2Answer 3Answer 4

 

Where the tables would have the data:

 

Questions

question_text

1Question 1

2Question 2

 

Answers

answer_idquestion_iduser_idanswer_text

111Answer 1

221Answer 2

312Answer 3

422Answer 4

 

The row headings in the table we want to create are the question_text from the questions table, and thats about where we've got stuck.. Most we got was:

 

SELECT
  `questions`.`question_text` AS `Question`,
  `answers`.`answer_text` AS `Answer`,
  `answers`.`user_id` AS `User_Id`
FROM `answers` LEFT JOIN `questions` USING (`question_id`) WHERE `answers`.`user_id` = '1'

 

But that just produces a list of each question and its answer.. e.g.

 

QuestionAnswerUser_Id

111

221

 

If anyone can help us out on this one, it would be much appreciated as we've not used Joins or anything more advanced than simple MySQL statements before and its quite confusing.