Issues trying to join multiple tables-CodePudding

The school in question has two different ways of promoting gifted students in the middle of the school year. The first to simply promote them to the next grade; the other is to "dual enroll" them in two grades at once. However, when we give the students their schedule, we just want to report the highest grade in which the student is enrolled. The query should return said information for any date. If you were to run this query over the sample data for ‘01/15/2013’, it would return:

| name  | grade | className |
|-------|-------|-----------|
| Adam  | 8     | Science   |
| Adam  | 8     | English   |
| Alice | 7     | English   |
| Alice | 7     | History   |
| Jack  | 7     | English   |
| Jack  | 7     | Math      |

Here are what the tables are structured like:

CREATE TABLE dbo.[Class]
(
    classID INT NOT NULL PRIMARY KEY,
    name VARCHAR(50)
)

CREATE TABLE dbo.[Person]
(
    personID INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    parentID1 INT,
    parentID2 INT,
    gender VARCHAR(10)
)


CREATE TABLE dbo.AttendanceRecord
(
    attendanceID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    personID INT,
    classID INT,
    [date] SMALLDATETIME,
    CONSTRAINT FK_attendancePerson FOREIGN KEY (personID) REFERENCES Person(personID),
    CONSTRAINT FK_attendanceClass FOREIGN KEY (classID) REFERENCES Class(classID)
)


CREATE TABLE dbo.Schedule
(
    personID INT,
    classID INT,
    CONSTRAINT FK_schedulePerson FOREIGN KEY (personID) REFERENCES Person(personID),
    CONSTRAINT FK_scheduleClass FOREIGN KEY (classID) REFERENCES Class(classID)
)

CREATE TABLE dbo.Enrollment 
(
    personID INT,
    startDate SMALLDATETIME,
    endDate SMALLDATETIME,
    grade INT,
    CONSTRAINT FK_enrollmentPerson FOREIGN KEY (personID) REFERENCES Person(personID)
)

Here is the sample data I am working with:

INSERT INTO dbo.AttendanceRecord
SELECT 25,123,'03/21/2013'
UNION
SELECT 25,456,'03/21/2013'
UNION
SELECT 25,789,'03/21/2013'
UNION
SELECT 13,147,'03/21/2013'
UNION
SELECT 25,123,'03/22/2013'
UNION
SELECT 82,147,'03/22/2013'
UNION
SELECT 82,456,'03/22/2013'
UNION
SELECT 13,147,'03/23/2013'
UNION
SELECT 25,456,'03/23/2013'
UNION
SELECT 13,456,'03/24/2013'
UNION
SELECT 25,456,'03/28/2013'


INSERT INTO dbo.Class
SELECT 123, 'Math'
UNION
SELECT 456, 'English'
UNION
SELECT 789, 'History'
UNION 
SELECT 258, 'Lunch'
UNION
SELECT 147, 'Science'

INSERT INTO dbo.Person
SELECT 25, 'Jack', 255, NULL, 'Male'
UNION
SELECT 13, 'Jill', 100, 101, 'Female'
UNION
SELECT 82, 'Adam', NULL, 200, 'Male'
UNION
SELECT 100, 'Jeff', NULL, NULL, 'Male'
UNION
SELECT 101, 'June', NULL, NULL, 'Female'
UNION
SELECT 200, 'Shirley', NULL, NULL, 'Female'
UNION
SELECT 155, 'Alice', 255, NULL, 'Female'
UNION
SELECT 255, 'Tom', NULL, NULL, 'Male'
UNION
SELECT 19, 'Jake', 100,101, 'Male'


INSERT INTO dbo.Schedule
SELECT 25,123
UNION
SELECT 25,456
UNION
SELECT 25,789
UNION
SELECT 13,147
UNION
SELECT 82,147
UNION
SELECT 82,456
UNION
SELECT 13,456
UNION
SELECT 19,123
UNION
SELECT 19,789
UNION
SELECT 155,456
UNION
SELECT 155,789


INSERT INTO dbo.Enrollment
SELECT 25, '09/01/2012','05/31/2013', 7
UNION
SELECT 25, '03/01/2013','05/31/2013', 8
UNION
SELECT 13, '09/01/2012','05/31/2013', 7
UNION
SELECT 82, '09/01/2012', '12/31/2012', 7
UNION
SELECT 82, '01/01/2013', '05/31/2013', 8
UNION
SELECT 19, '09/01/2012', '05/31/2013', 8
UNION
SELECT 155, '09/01/2012','05/31/2013', 7

I have tried using multiple joins to achieve this query but nothing seems to be working. It either shows me a huge list of results I dont want or it throws errors.

SELECT P.name, E.grade, C.name AS className
FROM Schedule AS S
JOIN Class AS C ON C.classid = S.classid
JOIN Person AS P ON P.personid = S.personid
JOIN Enrollment AS E ON E.personid = P.personid

Now I know this isnt what I need, but its where I am starting. Not sure if this is the correct direction to be heading

CodePudding user response：

Your data is not clear and the result provide issues.however the query is

SELECT P.NAME,
       C.NAME,
       grade
FROM   (SELECT personid,
               classid,
               Max(grade) grade
        FROM  (SELECT E.personid,
                      A.classid,
                      E.grade
               FROM   (SELECT personid,
                              classid,
                              Min([date]) startDate,
                              Max([date]) endDate
                       FROM   AttendanceRecord
                       GROUP  BY personid,
                                 classid) A
                      JOIN Enrollment E
                        ON E.personid = A.personid
              ---AND A.startDate=E.startDate AND A.endDate=E.endDate
--Data is not clear and it provide no result
              ) AA
        GROUP  BY personid,
                  classid)AB
       JOIN Class C
         ON C.classid = AB.classid
       JOIN Person P
         ON P.personid = AB.personid