LeftJoin on Multiple tables

ManMohabe · March 15, 2019, 11:55am

I have three tables
Student table with StudentID as primary key, ProgrammeID column,SpecilizationID
Marks table with StudentID,SemesterID ,CourseID as primary keys
Course table with CourseID,SemesterID,ProgrameID as primary keys, specializationID
My expectations is to left join marks table on course table and pick courses that are not in the Marks table from the course table
The reason for joining with the student table is to get the ProgrammeID & specializationID and use it to get the courses belonging to the programmeID & specializationID from the course table therefore the student.
This is what am doing
SELECT *
from Courses AS C left join
(select s.ProgrammeID,s.SpecializationID,m.CourseID
from ( select* from Students where StudentID=1 and Registered='true') as S
left join Marks as M on s.StudentID=m.StudentID ) as sm on sm.ProgrammeID=C.ProgrammeID and sm.SpecializationID=C.SpecializationID and sm.CourseID=C.CourseID
where sm.CourseID is null order by c.SemesterID

The query returns also Courses that are in the Marks table which should not be the case

harishgg1 · March 15, 2019, 1:15pm

hi

what about this ?

hope this helps

select * from #course b left join #marks a
on a.CourseID= b.CourseID and a.SemesterID = b.SemesterID
where a.CourseID is null

ManMohabe · March 15, 2019, 6:35pm

Thanks although I need to get only courses that belong to a particular student that's why I need to join to the student to filter the course table with programmeID.

jeffw8713 · March 18, 2019, 4:26pm

Can you provide sample data (as insert statements) and expected results - along with table definitions (create table statements)?

It isn't clear what you are trying to accomplish - do you want all students that have a course where there are no marks for that course? Or do you want all courses and those students with that course where there are no marks for the course? Something else?

ManMohabe · March 19, 2019, 5:49am

This is what i have managed to come up with

SELECT C.SemesterID AS SemesterID, C.CourseID AS CourseID, C.CourseName AS Course, C.Description AS Description  
                  from Courses C inner join Students s on c.ProgrammeID=s.ProgrammeID and  c.SpecializationID = s.SpecializationID and s.StudentID=1 left join  
                    Marks M on  C.CourseID=M.CourseID and C.SemesterID=M.SemesterID and M.StudentID=1 
                    where M.CourseID is null
                    order by SemesterID

And its the results

SemesterID	CourseID	Course	Description
2	B0410	Company Law	Core
2	B0411	Financial Accounting	Core
2	B0412	Human Resource	Core
2	B0413	Marketing	Core
2	B0414	Public Sector Accounting	Core
2	B048	Introduction to financial Management	Core
2	B049	Commercial Law	Core

CREATE TABLE [dbo].[Students] (
    [StudentID]        INT            IDENTITY (1, 1) NOT NULL,
    [StudentNo]        NVARCHAR (25)  NULL,
    [Registered]       BIT            DEFAULT ((0)) NOT NULL,
    [ProgrammeID]      NVARCHAR (10)  NOT NULL,
    [AdmissionDate]    DATETIME       NULL,
    [Year]             INT            NOT NULL,
    [IntakeID]         NVARCHAR (5)   NOT NULL,
    [Sponsor]          NVARCHAR (MAX) NOT NULL,
    [StudyMode]        NVARCHAR (MAX) NOT NULL,
    [SpecializationID] INT            NULL,
    CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED ([StudentID] ASC),
    CONSTRAINT [Fk_Students_Intakes] FOREIGN KEY ([IntakeID]) REFERENCES [dbo].[Intakes] ([IntakeID]) ON DELETE CASCADE,
    CONSTRAINT [Fk_Students_Programmes] FOREIGN KEY ([ProgrammeID]) REFERENCES [dbo].[Programmes] ([ProgrammeID]) ON DELETE CASCADE,
    CONSTRAINT [FK_Students_Specializations_SpecializationID] FOREIGN KEY ([SpecializationID]) REFERENCES [dbo].[Specializations] ([SpecializationID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Students_IntakeID]
    ON [dbo].[Students]([IntakeID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Students_ProgrammeID]
    ON [dbo].[Students]([ProgrammeID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Students_SpecializationID]
    ON [dbo].[Students]([SpecializationID] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Students_StudentNo]
    ON [dbo].[Students]([StudentNo] ASC) WHERE ([StudentNo] IS NOT NULL);

--Marks Table

CREATE TABLE [dbo].[Marks] (
    [StudentID]     INT           NOT NULL,
    [SemesterID]    INT           NOT NULL,
    [CourseID]      NVARCHAR (10) NOT NULL,
    [PeriodID]      INT           NOT NULL,
    [Approved]      BIT           DEFAULT ((0)) NOT NULL,
    [Released]      BIT           DEFAULT ((0)) NOT NULL,
    [PostingClosed] BIT           DEFAULT ((0)) NOT NULL,
    [CAT]           INT           DEFAULT ((-1)) NOT NULL,
    [FinalExam]     INT           DEFAULT ((-1)) NOT NULL,
    [Supplementary] INT           DEFAULT ((-1)) NOT NULL,
    [CourseMarks]   AS            (case when [CAT]>(-1) AND [FinalExam]>(-1) AND [Supplementary]=(-1) then CONVERT([nvarchar](3),[CAT]+[FinalExam]) when [CAT]>(-1) AND [FinalExam]>(-1) AND [Supplementary]>(1) then CONVERT([nvarchar](3),[CAT]+[Supplementary])+'*' when [CAT]>(-1) AND [FinalExam]<(0) OR [CAT]<(0) AND [FinalExam]>(0) then CONVERT([nvarchar](3),'INC') else 'DNS' end),
    CONSTRAINT [PK_Marks] PRIMARY KEY CLUSTERED ([StudentID] ASC, [CourseID] ASC, [SemesterID] ASC),
    CONSTRAINT [FK_Marks_Periods_PeriodID] FOREIGN KEY ([PeriodID]) REFERENCES [dbo].[Periods] ([PeriodID]),
    CONSTRAINT [FK_Marks_Student] FOREIGN KEY ([StudentID]) REFERENCES [dbo].[Students] ([StudentID]),
    CONSTRAINT [FK_Marks_Courses_CourseID_SemesterID] FOREIGN KEY ([CourseID], [SemesterID]) REFERENCES [dbo].[Courses] ([CourseID], [SemesterID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Marks_PeriodID]
    ON [dbo].[Marks]([PeriodID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Marks_CourseID_SemesterID]
    ON [dbo].[Marks]([CourseID] ASC, [SemesterID] ASC);


CREATE TABLE [dbo].[Courses] (
    [SemesterID]       INT            NOT NULL,
    [CourseID]         NVARCHAR (10)  NOT NULL,
    [ProgrammeID]      NVARCHAR (10)  NOT NULL,
    [SpecializationID] INT            NULL,
    [CourseName]       NVARCHAR (50)  NULL,
    [Description]      NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED ([CourseID] ASC, [SemesterID] ASC),
    CONSTRAINT [FK_Courses_Specializations_SpecializationID] FOREIGN KEY ([SpecializationID]) REFERENCES [dbo].[Specializations] ([SpecializationID]),
    CONSTRAINT [Fk_Courses_Programmes] FOREIGN KEY ([ProgrammeID]) REFERENCES [dbo].[Programmes] ([ProgrammeID]),
    CONSTRAINT [FK_Semesters_Courses] FOREIGN KEY ([SemesterID]) REFERENCES [dbo].[Semesters] ([SemesterID])
);


GO
CREATE NONCLUSTERED INDEX [IX_Courses_ProgrammeID]
    ON [dbo].[Courses]([ProgrammeID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Courses_SemesterID]
    ON [dbo].[Courses]([SemesterID] ASC);


GO
CREATE NONCLUSTERED INDEX [IX_Courses_SpecializationID]
    ON [dbo].[Courses]([SpecializationID] ASC);



SET IDENTITY_INSERT [dbo].[Students] ON
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (1, N'B04/J/00001/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', 1)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (2, N'B04/J/00003/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (3, N'B04/J/00004/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (4, N'B04/J/00005/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (5, N'B04/J/00006/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (6, N'B04/J/00007/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (7, N'B04/J/00008/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (8, N'B04/J/00009/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (9, N'B04/J/00010/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (10, N'B04/J/00011/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (11, N'B04/J/00012/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (12, N'B04/J/00013/2019', 1, N'B04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
INSERT INTO [dbo].[Students] ([StudentID], [StudentNo], [Registered], [ProgrammeID], [AdmissionDate], [Year], [IntakeID], [Sponsor], [StudyMode], [SpecializationID]) VALUES (13, N'D04/J/00001/2019', 1, N'D04', N'2019-03-15 11:53:10', 2019, N'J', N'Government', N'Parttime', NULL)
SET IDENTITY_INSERT [dbo].[Students] OFF


INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B041', N'B04', 1, N'Introduction to Computers', N'Elective')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B0410', N'B04', 1, N'Company Law', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B0411', N'B04', 1, N'Financial Accounting', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B0412', N'B04', 1, N'Human Resource', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B0413', N'B04', 1, N'Marketing', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B0414', N'B04', 1, N'Public Sector Accounting', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B042', N'B04', 1, N'Communication Skills', N'Elective')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B043', N'B04', 1, N'Business Maths', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B044', N'B04', 1, N'Introduction to Statistics', N'Elective')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B045', N'B04', 1, N'Micro Economics', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B046', N'B04', 1, N'Introduction to Macro economics', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (1, N'B047', N'B04', 1, N'Financial Accounting 1', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B048', N'B04', 1, N'Introduction to financial Management', N'Core')
INSERT INTO [dbo].[Courses] ([SemesterID], [CourseID], [ProgrammeID], [SpecializationID], [CourseName], [Description]) VALUES (2, N'B049', N'B04', 1, N'Commercial Law', N'Core')

INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B041', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B042', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B041', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B042', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (2, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B041', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B042', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (3, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B041', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B042', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (4, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B041', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B042', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (5, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B041', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B042', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (6, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)

jeffw8713 · March 19, 2019, 2:42pm

And what are your expected results?

ManMohabe · March 20, 2019, 7:33am

My expected results, Is to get a particular student Courses that are not yet added in the Marks tables(inserted).

jeffw8713 · March 20, 2019, 8:45pm

Sorry - but can you show what that looks like, given the sample data you provided?

ManMohabe · March 21, 2019, 5:25am

Hi jeffw8713,Thanks for your Concern. My expected results is to join the student table to the Course table and pick only the Courses that are relevant to the particular student such that where the student.programmeID=Course.ProgrammeId and Student.SpecializationID =Course.SpecializationId where studentID is like 1,2,3 etc(This is parameter). The results should be Left join with the Marks table and pick only Courses that are not in the in the marks Table for that particular student. For instance a student with ID (StudentID) 8 does not appear in the Marks table therefore am supposed to get all the Courses that are relevant to him where his ProgrammID is "B04" and has a SpecializationID is null from the Course table incase we had Courses that had a null specializationID, for a student with ID (StudentID) 1 has some Courses in the Marks table through the folllowing insert

INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B043', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B044', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B045', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B046', 1, 0, 0, 0, -1, -1, -1)
INSERT INTO [dbo].[Marks] ([StudentID], [SemesterID], [CourseID], [PeriodID], [Approved], [Released], [PostingClosed], [CAT], [FinalExam], [Supplementary]) VALUES (1, 1, N'B047', 1, 0, 0, 0, -1, -1, -1)

As Shown above, therefore am supposed to return the following courses as my results from the the courses table

SemesterID	CourseID	Course	Description
2	B0410	Company Law	Core
2	B0411	Financial Accounting	Core
2	B0412	Human Resource	Core
2	B0413	Marketing	Core
2	B0414	Public Sector Accounting	Core
2	B048	Introduction to financial Management	Core
2	B049	Commercial Law	Core
since they are not yet inserted in the Marks table

This is what i have managed to come up with, Somehow working. I will appreciate any suggestion.

SELECT C.SemesterID AS SemesterID, C.CourseID AS CourseID, C.CourseName AS Course, C.Description AS Description  
                  from Courses C inner join Students s on c.ProgrammeID=s.ProgrammeID and  c.SpecializationID = s.SpecializationID and s.StudentID=1 left join  
                    Marks M on  C.CourseID=M.CourseID and C.SemesterID=M.SemesterID and M.StudentID=1 
                    where M.CourseID is null
                    order by SemesterID

I hope this is clear now, Sorry for the confusion i have created. Thanks

jeffw8713 · March 21, 2019, 6:33pm

I don't see a problem with your query - as written...

I would write it this way:

 Select s.StudentID
      , c.SemesterID As SemesterID
      , c.CourseID As CourseID
      , c.CourseName As Course
      , c.Description As description
   From dbo.Student             s
  Inner Join dbo.Courses        c On c.ProgrammeID = s.ProgrammeID
                                 And c.SpecializationID = s.SpecializationID
   Left Join dbo.Marks          m On m.StudentID = s.StudentID
                                 And m.CourseID = c.CourseID 
                                 And m.SemesterID = c.SemesterID
  Where s.StudentID = 1
    And m.CourseID Is Null
  Order By
        c.SemesterID;

What is missing here is the table that lists the student/semester/course - which would identify those courses that the student has enrolled in for that semester.

This query will return any student that does not have marks for any course associated by program/specialization - but I would assume that all students are not enrolled in all courses (in the same semester) and would not expect to have marks for those students that have not enrolled for those courses in that semester.

If all courses are required - and they are required during each semester for each student - then this should work.

ManMohabe · March 22, 2019, 8:41am

Thanks very much, this is what i needed