Singhal2 Singhal2 - 6 months ago 11
SQL Question

Condition on multiple values in the same column in SQL

Firstly, thanks in advance for helping. This will be my first question on SOF.

I have the following SQL database tables.

qualificationTable
:

QualId studentNo CourseName Percentage

1 1 A 91
2 1 B 81
3 1 C 71
4 1 D 61
5 2 A 91
6 2 B 81
7 2 C 71
8 2 D 50


testTable
:

TestId studentNo testNo Percentage

1 1 1 91
2 1 2 81
3 1 3 71
4 2 1 80
5 2 2 99
6 2 3 87


I have the minimum percentages specified for courses A, B, C and D individually. I need to search for students, meeting the minimum criteria for ALL the courses.

Part-2:
That student should also match the criteria (minimum percentages specified individually for the three tests- 1,2 and 3) in testTable.

In other words, if a student matches the minimum criteria (percentage) specified individually for all the courses, he should be selected. Now, same goes for the testTable, that particular student (who got selected in qualificationTable) should have the minimum criteria (percentage) specified individually for the three tests (1,2 and 3) in testNo column.

I am trying something like this:

SELECT `studentNo`, `CourseName`, `Percentage`
FROM `qualificationTable`
WHERE `CourseName` IN ('A', 'B')
AND `Percentage` >= 71
GROUP BY `studentNo`
HAVING COUNT(*) = 2


Test Case:

Minimum qualification percentage required:


Course A: 90 Course B: 80 Course C: 70 Course D: 60


Minimum tests percentage required:


Test 1: 90 Test 2: 80 Test 3: 70


Should return studentNo 1 as he meets the minimum criteria for all cases. Also, there are going to be thousands of students in the record.

Expected Output

studentNo

1


Thanks a lot once again.

Answer

I've just figured it out for your sample data and Test Case:

Minimum qualification percentage required:

Course A: 90 Course B: 80 Course C: 70 Course D: 60

Minimum tests percentage required:

Test 1: 90 Test 2: 80 Test 3: 70

Try this, may help for you;)

SQL Fiddle

MySQL Schema:

CREATE TABLE qualificationTable
    (`QualId` int, `studentNo` int, `CourseName` varchar(1), `Percentage` int)
;

INSERT INTO qualificationTable
    (`QualId`, `studentNo`, `CourseName`, `Percentage`)
VALUES
    (1, 1, 'A', 91),
    (2, 1, 'B', 81),
    (3, 1, 'C', 71),
    (4, 1, 'D', 61),
    (5, 2, 'A', 91),
    (6, 2, 'B', 81),
    (7, 2, 'C', 71),
    (8, 2, 'D', 50)
;


CREATE TABLE testTable
    (`TestId` int, `studentNo` int, `testNo` int, `Percentage` int)
;

INSERT INTO testTable
    (`TestId`, `studentNo`, `testNo`, `Percentage`)
VALUES
    (1, 1, 1, 91),
    (2, 1, 2, 81),
    (3, 1, 3, 71),
    (4, 2, 1, 80),
    (5, 2, 2, 99),
    (6, 2, 3, 87)
;

Query 1:

select t1.studentNo
from 
(
  select studentNo from qualificationTable
  where (CourseName = 'A' and Percentage >= 90)
  or (CourseName = 'B' and Percentage >= 80)
  or (CourseName = 'C' and Percentage >= 70)
  or (CourseName = 'D' and Percentage >= 60)
  group by studentNo
  having count(1) = 4
) t1 join
( select studentNo from testTable
  where (testNo = '1' and Percentage >= 90)
  or (testNo = '2' and Percentage >= 80)
  or (testNo = '3' and Percentage >= 70)
  group by studentNo
  having count(1) = 3
) t2 on t1.studentNo = t2.studentNo

I just pick t1 one of these two subquery to explain how it works:

  • GROUP BY can get us a result like this,
    | studentNo |
    |-----------|
    |         1 |
    |         2 |
  • COUNT will get us total count of each group, for your sample data, studentNo(1) is 4, studentNo(2) is 4 as well, but we also has where clause here, so by these criteria, we can find which matched are following record,
    (1, 1, 'A', 91),
    (2, 1, 'B', 81),
    (3, 1, 'C', 71),
    (4, 1, 'D', 61),
    (5, 2, 'A', 91),
    (6, 2, 'B', 81),
    (7, 2, 'C', 71)
  • And this means COUNT will give us studentNo(1) to 4, studentNo(2) to 3, so when mysql run having count(1) = 4, this subquery only return us studentNo(1)

Subquery t2 works like that, and when join these two subquery by studentNo, it will return what you expected result.

Results:

| studentNo |
|-----------|
|         1 |
Comments