Singhal2 - 1 year ago 38

SQL Question

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

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.

`studentNo`

1

Thanks a lot once again.

Answer Source

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;)

**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.

```
| studentNo |
|-----------|
| 1 |
```