CODEWITHSUNDEEP
sqloracle11g
tumchaaditya
tumchaaditya

Reputation: 1297

SQL - Getting students who passed in all subjects

I have a database table with some fields and sample records as shown below: 

Name    Test    Result  Other_fields  
A      English    P         x  
B      Maths      F         x  
C      English    P         x  
B      English    P         x  
A      Maths      F         x  
D      English    P         x  
C      Biology    F         x  
A      Biology    P         x  
B      Biology    F         x

Now the actual table contains a lot more variety of tests.
What I want to achieve is find count/list of students(and the corresponding values in other fields which are unique for a student...say his age, sex etc.) who passed in all the tests specified in a comma-separated string. The string obviously is inserted into query at run-time.

Now if it were a fixed number of tests say 2 tests or 3 tests then I could write a query for it.
But the real problem is number of tests in the CSV string is not fixed.
Is there any way I could achieve this?

PS: I am okay to substitute test name with some unique number(to function as test ID) if one plans to suggest an answer involving use of SQL IN.

EDIT

Apparently, I did not make myself quite clear. The table will contain records for a large number of tests(say test1, test2,....test100). The CSV string will contain a few of those tests(say 2 or 3 or 4 but definitely more than 1). And I want the data where student passed in all the tests specified by CSV string.

EDIT 2
There can be multiple records corresponding to one combination of a student and a test(multiple times pass and fail)

Upvotes: 3

Views: 4580

Answers (2)

jmoreno
jmoreno

Reputation: 13551

You want to look for how to turn a csv into a table. I've never done this for Oracle, but it should be fairly easy to do.

Alternatively, consider whether you really need to send a csv, or could you send multiple parameters. Something like Test in (param1,param2,....paramX). If you allow a limited number of parameters, this is viable. It of course won't work if you are sending thousands of parameters, imo a dozen is pushing it, but if you are always sending like 3-5, it works just fine.

Edit: Here's a sqlfiddle that shows basically what I mean and how to do it. It splits a string, joins the string, counts the number of matching test, and list the student/test information where the student passed all of the incoming test. My original example had a join on 1=1 because I was having a trouble with column names and I wanted to show the join happening.

With t as (
Select 'English, Biology' as Tests from dual
),
Spl as(
Select rtrim(ltrim(REGEXP_SUBSTR (Tests, '[^,]+', 1, level))) As Tst,
 regexp_count(Tests,'[,]')+1 NumberOfTest
from t
connect by level<= regexp_count(Tests,'[,]')+1
)
, StudentPassing as (
Select count(TestName) over (partition by StudentName) TestPassed, 
    s.*, NumberOfTest
from StudentTest s 
  inner join Spl 
    on Tst=TestName
where TestResult='P'
)
select *
from StudentPassing
where TestPassed=NumberOfTest

Upvotes: 1

Avo Murom&#228;gi
Avo Murom&#228;gi

Reputation: 1593

SELECT DISTINCT Name 
  FROM Table t
 WHERE NOT EXISTS(SELECT 1
                    FROM Table 
                   WHERE     t.Name = Name 
                         AND Result = 'F'
                         AND ',' || CsvOfTests || ',' LIKE '%,' || Test || ',%'
                 )

I would recommend using some unique value like auto-incremented id instead of the name here. If you want to have additional fields in the result set you can include them in your SELECT and GROUP BY field list or by using a join.

CsvOfTests is the list of tests that you are interested of. I added commas to the beginning and the end of the CsvOfTests and Test in case name of one test is the substring of the name of some other test. However I have to say that I feel that the query would be much simpler and less hacky if you would put the tests into temporary table instead of csv string.

If your input string separates tests by more than just a comma (like with a space after a comma you will need include those aswell). If it's more complicated than that then I guess I'd try REGEXP_LIKE or write my own function.

Per request the query that counts passed subjects

SELECT DISTINCT Name 
  FROM Table t
 WHERE LENGTH(CsvOfTests) - LENGTH(REPLACE(CsvOfTests, ',')) + 1 = (
                  SELECT COUNT(1)
                    FROM Table 
                   WHERE     t.Name = Name 
                         AND Result = 'P'
                         AND ',' || CsvOfTests || ',' LIKE '%,' || Test || ',%'
                 )

Here LENGTH(CsvOfTests) - LENGTH(REPLACE(CsvOfTests, ',')) + 1 counts the number of tests in your list. Then it justs counts how many of those tests the student has passed. Apparently there's also REGEXP_COUNT to do the same in 11g Release 1.

After I reread and thought about your question, I understood that this was not exactly that you were asking for. The goal of this query more on the line of checking whether student has passed each subject (i.e. if you fail the exam the first time then you retake it and eventually pass).

Upvotes: 4

Related Questions