Adding Additional Tables to an Existing Query

Dear All

I followed the advice and created the query again from scratch, one table and relationship at a time checking that I am getting the expected result. I have put much of the query back together with the correct output:

SELECT
  SENAlert.SENAlertID,
  SENAlertType.SENAlertTypeDescription
FROM dbo.SENAlert
INNER JOIN dbo.SENAlertType
  ON SENAlert.SENAlertType = SENAlertType.SENAlertTypeID
INNER JOIN dbo.Student
  ON SENAlert.SENAlertStudentID = Student.StudentID
INNER JOIN dbo.ClassMember
  ON ClassMember.ClassMemberStudentID = Student.StudentID
INNER JOIN dbo.Class
  ON ClassMember.ClassMemberClassCode = Class.ClassClassCode
INNER JOIN dbo.Teacher
  ON Class.ClassTeacherCode = Teacher.TeacherCode
LEFT OUTER JOIN dbo.Teacher AdditionalTeacher1
  ON AdditionalTeacher1.TeacherCode = Class.ClassAdditionalTeacherCode1
LEFT OUTER JOIN dbo.Teacher AdditionalTeacher2
  ON AdditionalTeacher2.TeacherCode = Class.ClassAdditionalTeacherCode2
LEFT OUTER JOIN dbo.Teacher AdditionalTeacher3
  ON AdditionalTeacher3.TeacherCode = Class.ClassAdditionalTeacherCode3
WHERE Student.StudentID = 011763
AND (Teacher.TeacherUsername = 'admin\dsmith'
OR AdditionalTeacher1.TeacherUsername = 'admin\dsmith'
OR AdditionalTeacher2.TeacherUsername = 'admin\dsmith'
OR AdditionalTeacher3.TeacherUsername = 'admin\dsmith')

The problem arises when I try to add the SENAlertHistory table back in. I only want to show results where a related record does not exist in SENAlertHistory. I have tried every combination of relationship but none have been successful.

I think maybe I need to be using a subquery instead.

Thanks for your help

Daniel

do you mean this?

SELECT
  SENAlert.SENAlertID,
  SENAlertType.SENAlertTypeDescription
FROM dbo.SENAlert
INNER JOIN dbo.SENAlertType
  ON SENAlert.SENAlertType = SENAlertType.SENAlertTypeID
INNER JOIN dbo.Student
  ON SENAlert.SENAlertStudentID = Student.StudentID
INNER JOIN dbo.ClassMember
  ON ClassMember.ClassMemberStudentID = Student.StudentID
INNER JOIN dbo.Class
  ON ClassMember.ClassMemberClassCode = Class.ClassClassCode
INNER JOIN dbo.Teacher
  ON Class.ClassTeacherCode = Teacher.TeacherCode
AND NOT EXISTS ( SELECT 1
FROM SENAlertHistory
WHERE SENALertHistoryTeacherCode = Teacher.TeacherCode
AND SENAlertHistorySENAlertID = SENAlert.SENAlertID
)
LEFT OUTER JOIN dbo.Teacher AdditionalTeacher1
  ON AdditionalTeacher1.TeacherCode = Class.ClassAdditionalTeacherCode1
  AND NOT EXISTS (SELECT 1
  FROM SENAlertHistory
  WHERE SENAlertHistoryTeacherCode = AdditionalTeacher1.TeacherCode
  AND SENAlertHistorySENAlertID = SENAlert.SENAlertID
)
LEFT OUTER JOIN dbo.Teacher AdditionalTeacher2
  ON AdditionalTeacher2.TeacherCode = Class.ClassAdditionalTeacherCode2
 AND NOT EXISTS (SELECT 1
  FROM SENAlertHistory
  WHERE SENAlertHistoryTeacherCode = AdditionalTeacher2.TeacherCode
  AND SENAlertHistorySENAlertID = SENAlert.SENAlertID
)
LEFT OUTER JOIN dbo.Teacher AdditionalTeacher3
  ON AdditionalTeacher3.TeacherCode = Class.ClassAdditionalTeacherCode3
 AND NOT EXISTS (SELECT 1
  FROM SENAlertHistory
  WHERE SENAlertHistoryTeacherCode = AdditionalTeacher3.TeacherCode
  AND SENAlertHistorySENAlertID = SENAlert.SENAlertID
)
WHERE Student.StudentID = 011763
AND (Teacher.TeacherUsername = 'admin\dsmith'
OR AdditionalTeacher1.TeacherUsername = 'admin\dsmith'
OR AdditionalTeacher2.TeacherUsername = 'admin\dsmith'
OR AdditionalTeacher3.TeacherUsername = 'admin\dsmith')

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c