-
[RESOLVED] SQL issue!
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
-
Re: SQL issue!
You have to use inner join to relate tables to each other:
Code:
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:
Code:
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:
Code:
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;