If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help to select record by checking multiple child items
Hello,
I have a table consisting of student names linked to another one with the date and status of passed tests (one to many relation). It does happen that a student passes after the 1st test or needs sometimes to take it several times before succeeding. We have something looking like: Student 1 - date 1st test - result 1st test (failed) Student 1 - date 2nd test - result 2nd test (passed) Student 2 - date 1st test - result 1sr test (failed) Student 2 - date 2nd test - result 2nd test (failed) Student 2 - date 3rd test - result 3rd test (failed) I'm trying to design a query that would only return the name of the students who didn't succeed and consequently exclude the name of those who did pass, even if after the xth attempt. In the above example, the query should therefore return: Student 2 only listed once, for the clarity of the report. Any advice would be appreciated! Regards, Renaud, Brussels |
#2
|
|||
|
|||
Help to select record by checking multiple child items
On Jul 4, 7:42*am, Renaud_D wrote:
Hello, I have a table consisting of student names linked to another one with the date and status of passed tests (one to many relation). It does happen that a student passes after the 1st test or needs sometimes to take it several times before succeeding. We have something looking like: Student 1 - date 1st test - result 1st test (failed) Student 1 - date 2nd test - result 2nd test (passed) Student 2 - date 1st test - result 1sr test (failed) Student 2 - date 2nd test - result 2nd test (failed) Student 2 - date 3rd test - result 3rd test (failed) I'm trying to design a query that would only return the name of the students who didn't succeed and consequently exclude the name of those who did pass, even if after the xth attempt. In the above example, the query should therefore return: * Student 2 only listed once, for the clarity of the report. Any advice would be appreciated! Regards, Renaud, Brussels Try one of these methods: SELECT A.StudentID, A.StudentName from Students as A where 0 = ( select count(*) from StudentGrades where StudentID = A.StudentID and Result = 'Passed' ) or SELECT A.StudentID, A.StudentName from Students as A where not exists ( select 'true' from StudentGrades where StudentID = A.StudentID and Result = 'Passed' ) or SELECT A.StudentID, A.StudentName from Students as A LEFT JOIN StudentGrades as B on A.StudentID = B.StudentID where B.Result = 'Passed' and B.StudentID is null |
#3
|
|||
|
|||
Help to select record by checking multiple child items
Renaud_D wrote:
I have a table consisting of student names linked to another one with the date and status of passed tests (one to many relation). It does happen that a student passes after the 1st test or needs sometimes to take it several times before succeeding. We have something looking like: Student 1 - date 1st test - result 1st test (failed) Student 1 - date 2nd test - result 2nd test (passed) Student 2 - date 1st test - result 1sr test (failed) Student 2 - date 2nd test - result 2nd test (failed) Student 2 - date 3rd test - result 3rd test (failed) I'm trying to design a query that would only return the name of the students who didn't succeed and consequently exclude the name of those who did pass, even if after the xth attempt. In the above example, the query should therefore return: Student 2 only listed once, for the clarity of the report. SELECT DISTINCT student FROM table WHERE Not Exists (SELECT X.student FROM table As X WHERE X.result = "passed") -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|