Question about query

[user1980]

Hi there -

I have two tables, of which table 1 records who have opened an application and table 2 records their answers.

table 1:
Std_id, Name, Status

table 2:
Std_id, Q_id, Answers

where q_id is question id.
Table 1 has one record for each student who has answered questions/just opened an application and table 2 has multiple rows for each student. The table 2 has answers given by each student for 10 questions.

I want to extract the students who have opened the application and also want the answer for question number 5.
I was using the following query,

Select  app.Std_id,Name as ApplicantName, Status
, NVL((select TRIM (DBMS_LOB.SUBSTR (ans.Answers, 20, 1)) from tabl2  where Q_ID = 5 ), 'N.A') AS School 
 from tabl1  app 
inner join tabl2 ans on APP.Std_id = ANS.Std_id  WHERE  Status  in ('I','C')

this query returns multiple rows and I understand why.As there is an inner join and table 2 has different answers and q_id, so the join picks up every row from table 2. How can I avoid this,also the NVL on select does not work. It picks up all the answers. I want to pick only answer for q_id 5.

can some one please guide me on how can I achieve just one row per student entry. thank you.

[C CSR]

I think you need a "Group By" segment in the query

[user1980]

I tried it and unfortunately that does not work.thank you

[C CSR]

Try some form of this:

SELECT Questions.qID, Students.StudentID, Results.Answer, Results.RecID
FROM Students INNER JOIN (Questions INNER JOIN Results ON Questions.qID = Results.qID) ON Students.StudentID = Results.StudentID
WHERE (((Questions.qID)=2));
 

3 tables: Students, Questions, Results (Results contains the Question ID and the Student ID).

It gets the question you need, pulls all students answering that question and gives answers.

[C CSR]

Note: The Results tbl is akin to your "table2". The Student tbl should match your "Table1," and what I did differently besides changing the nature of the query is to split off your Questions into a separate table. Inner Join and enjoy...

[user1980]

thank you so much for the suggestion. I will work on this.Your query works as required but it does not pick up students who have no record corresponding to the question id 2 ie students can have a record in the answers table and students table even if they have no answer for that question.
I will follow your lead and try to figure how it can be achieved. thank you once again.

[C CSR]

I tested this thoroughly. It completes the Student, Questions and Answers triangle with the holes (unanswered questions) on one side. Its a Nested Select statement that finds the question you search on and produces the list of test-takers that did and did not answer all of the questions, showing the questions, answers and un-answers.

SELECT Z1.qID, Z1.Question, Z1.StudentID, Z1.Name, Results.RecID, Results.Answer
FROM (SELECT Questions.qID, Questions.Question, Students.StudentID, Students.Name 
FROM Questions, Students)  AS Z1 
LEFT JOIN Results ON (Z1.qID = Results.qID) AND (Z1.StudentID = Results.StudentID);
 

The setup is 3 tables: The first two are Questions [3] and Students [4]. Since I have 3 X 4 = 12, I should always have 12 records in my final query if do not specify any criteria. If I search on any one question, it produces 4 results (1 x 4) whether the question was answered or not.

For testing in my case, I set the 3rd table "Results" with only 10 questions answered because Student 4 didn't answer two in his set of three, and I assume he clicked "skip." (The last query I sent your way only produced the 10 "answered" records). By nesting a SELECT statement, I can get 12 results so you can see who didn't answer what (Student 4 is now listed 3 times, once for each question, but 2 of his answers are blank. I believe this is what you said you want.

I did this in Access so it can be pasted in as an SQL command manually and then you can see the graphic depiction. But Access will not automatically produce this query in the GUI by itself with a drag & drop. You may need to conform it to your platform. Of course, you'd have to set up 3 tables to accommodate the "aggregate attributes," (or field names), in my specific query. Otherwise create what you want and make some naming changes appropriately. Note that you have to apply an "alias" to the nested Select as a temporary table AND the outer attributes, designated in my sample as "Z1". The syntax is sensitive to minor changes in the structure but not with expanding the list of attributes.

Basic Definitions used for tables:

"Questions" Table contains its record IDs and the questions. "Students" contains its record IDs and the student names. "Results" will contain its record IDs, the student's Answers, and the corresponding Question IDs and Student IDs that I assume you would input programmatically as the Student signs in to take the test.

Let me know what you think if you still have time.

[C CSR]

You can use this query to create a final report or stick the results into tangible storage.

[user1980]

than you for the explanation. This is exactly what I was looking for.

thank you for your time.