[RESOLVED] SQL issue!

[Astro]

Hello there,

I would like to populate the ans_table.answer according to the ss_table.ansID

when the desired qnsID is selected.

here's a look at the 3 tables.


ss_table
______________________
| sID | qnsID | ansID|
----------------------


qns_table
____________________
| qnsID | question |
--------------------


ans_table
__________________
| ansID | answer |
------------------


below is my attempt excluding: WHERE ss_table_ansID = ans_table.ansID (because it prompts an error message)

----------------------------------------------------------------------------------------
rst.Open

"select distinct ss_table.ansID AS ss_table_ansID, ans_table.answer AS ans_table_answer

from ss_table, qns_table, ans_table

WHERE '" & List1.Column(0, List1.ListIndex) & "' = ss_table.qnsID"

, cn, adOpenDynamic, adLockOptimistic

-----------------------------------------------------------------------------------------

Can someone show me on how is it possible that i can do that?

Thank You

Astro

[Dnereb]

You have to use inner join to relate tables to each other:

SELECT ans_table.answer, qns_table.question
FROM ans_table INNER JOIN (qns_table INNER JOIN ss_table ON qns_table.qnsID = ss_table.qnsID) ON ans_table.ansID = ss_table.ansID;

the sql above would give all questions with all answers related to them.
this one question 1 with all answers:

SELECT ans_table.answer, qns_table.question
FROM ans_table INNER JOIN (qns_table INNER JOIN ss_table ON qns_table.qnsID = ss_table.qnsID) ON ans_table.ansID = ss_table.ansID
WHERE qns_table.qnsID = 1;

and this one all questions with answer 2:

SELECT ans_table.answer, qns_table.question
FROM ans_table INNER JOIN (qns_table INNER JOIN ss_table ON qns_table.qnsID = ss_table.qnsID) ON ans_table.ansID = ss_table.ansID
WHERE ans_table.ansID = 2;