SQL question

[Kelly_]

I have three tables:

QUESTIONS
questionID
surveyID

RESPONSES
responseID
surveyID

ANSWERS
answerID
questionNo
responseID

I need to query the answer table, and get the questionID for which they have answered. My problem is that for some reason when I designed the database, I didnt assign the answer to the questionId. Now I need to make an SQL query to get the questionID based on the question number.

My problems is that the SQL needs to be nested I think.

The answer has a responseID (ANSWERS TABLE)
the responseID has a surveyID (RESPONSES TABLE)
the surveyID can then get the questionID (QUESTIONS TABLE)

I've tried this


Code:
select
questions.questionID
FROM questions
right join (responses.surveyID on questions.surveyID)
ON questions.surveyID = responses.surveyID


but it's probably far from perfect - as it doesnt even work.
any ideas?!

Cheers!

 

[Daz]

SELECT *
FROM questions, responses, answers
WHERE questions.questionID = answers.questionNo
AND answers.responseID = responses.responseID

Maybe? May need adapting a little - that'll return everything.

Will need changing a bit, but that's basically doing it I think (top of my head.. I can't test it anywhere really.. )

 

[Kelly_]

Cheers Daz, it was something similar to that!

SET SESSION SQL_BIG_SELECTS = 1;
SELECT

questions.questionID,
questions.surveyID,
responses.responseID,
responses.surveyID,
answers.answerID,
answers.QuestionNo,
answers.responseID

FROM

(answers INNER JOIN responses ON answers.responseID = responses.ResponseID)

INNER JOIN questions ON responses.surveyID = questions.surveyID

WHERE answers.questionNo = "answer_1" AND answers.responseID = 10452 AND questions.questionNo = 1 AND type != "text"

Nightmare lol

 

[TomS]

kelly -


what?

 

[Daz]

Ah, mine joined the lot and gave you the lot, heh.

 

[Kelly_]

kelly -


what?

Nothing to worry your pretty little head about dear

When I think of a Dungeons and Dragons question I know who the expert is in that field!


Daz, thanks for helping.

 

[Has]

grrrr not SQL again

 

[Darren S]

Squeal 2000 - quality......

Supposed to be upgrading to SQL2005 PP but our camels-length-munching accounts package doesn't support it.

D.

 

[Daz]

Squeal 2000 - quality......

Not as good as MySQL tho