CODEWITHSUNDEEP
sqloracle-database
Martin Sommervold
Martin Sommervold

Reputation: 152

Getting a single result on one table from criteria on multiple rows in another table

Imagine a Student table with the name and id of students at a school, and a Grades table that has grades on the form:

grade_id | student_id.

What I want to do is find all the students that match an arbitrary criteria of say "find all students that have grade A, grade B, but not C or D".

In a school situation a student could have several A's and B's, but for my particular problem they will allways have one or none of each grade.

Also, the tables i'm working on are huge (several million rows in each), but i only need to find say 10-20 on each query (the purpose of this is to find test data).

Thanks!

Upvotes: 0

Views: 67

Answers (2)

nelucon
nelucon

Reputation: 326

Make sure all your id fields are indexed.

select * 
from students s
where exists
    (
        select * 
        from grades g 
        where g.grade_id in (1, 2) 
        and g.student_id = s.student_id
    )

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

Change the table variables to your physical tables and this should help?

DECLARE @Students TABLE (
    StudentId INT,
    StudentName VARCHAR(50));
INSERT INTO @Students VALUES (1, 'Tom');
INSERT INTO @Students VALUES (2, 'Dick');
INSERT INTO @Students VALUES (3, 'Harry');
DECLARE @StudentGrades TABLE (
    StudentId INT,
    GradeId INT);
INSERT INTO @StudentGrades VALUES (1, 1);
INSERT INTO @StudentGrades VALUES (1, 1);
INSERT INTO @StudentGrades VALUES (1, 2);
INSERT INTO @StudentGrades VALUES (1, 3);
INSERT INTO @StudentGrades VALUES (2, 1);
INSERT INTO @StudentGrades VALUES (2, 2);
INSERT INTO @StudentGrades VALUES (3, 1);
INSERT INTO @StudentGrades VALUES (3, 1);
INSERT INTO @StudentGrades VALUES (3, 3);
INSERT INTO @StudentGrades VALUES (3, 4);
INSERT INTO @StudentGrades VALUES (3, 4);
DECLARE @Grades TABLE (
    GradeId INT,
    GradeName VARCHAR(10));
INSERT INTO @Grades VALUES (1, 'A');
INSERT INTO @Grades VALUES (2, 'B');
INSERT INTO @Grades VALUES (3, 'C');
INSERT INTO @Grades VALUES (4, 'D');

--Student/ Grade Summary
SELECT
    s.StudentId,
    s.StudentName,
    g.GradeName,
    COUNT(sg.GradeId) AS GradeCount
FROM
    @Students s
    CROSS JOIN @Grades g
    LEFT JOIN @StudentGrades sg ON sg.StudentId = s.StudentId AND sg.GradeId = g.GradeId
GROUP BY
    s.StudentId,
    s.StudentName,
    g.GradeName;

--Find ten students with A and B but not C or D
SELECT TOP 10
    *
FROM
    @Students s

WHERE
    EXISTS (SELECT * FROM @StudentGrades sg WHERE sg.StudentId = s.StudentId AND sg.GradeId = 1) --Got an A
    AND EXISTS (SELECT * FROM @StudentGrades sg WHERE sg.StudentId = s.StudentId AND sg.GradeId = 2) --Got a B
    AND NOT EXISTS (SELECT * FROM @StudentGrades sg WHERE sg.StudentId = s.StudentId AND sg.GradeId IN (3, 4)); --Didn't get a C or D

Upvotes: 1

Related Questions