Detect absence of child record 1

[tis9700]

Hello,
I have a view that returns records based on whether an Instructor has not taught a class within a 22, 24 or 25 month period and what classes an Instructor has taught are outside the date range specified in the WHERE clause.

My problem is there might be Instructors who have never taught a class. I tried NOT EXISTS and NOT IN with no luck. Here's my latest attempt....

SELECT MAX(IC.certificationDate) AS MaxCertDate, I.instLName + ', ' + ISNULL(I.instSuffix, '') + ' ' + I.instFName + ' ' + ISNULL(I.instMName, '')
AS instFullName, I.instAddress, I.instCity + ', ' + I.instState + '. ' + I.instZip AS instFullCSZ, I.instSSN, IC.certificationID, MAX(C.classDate)
AS MaxClassDate, I.instID, TCC.CNTY_NAME AS workCounty, TCC.CNTY_REGION AS workRegion, IC.certificationType, IC.certificationStatus,
C.classType, DATEDIFF(m, IC.certificationDate, GETDATE()) AS monthFlag
FROM dbo.Instructors I INNER JOIN
dbo.Instructor_Certifications IC ON I.instID = IC.instID INNER JOIN
dbo.Classes C ON I.instID = C.instID INNER JOIN
dbo.Tn_Counties_Cat TCC ON I.instWorkCounty = TCC.CNTY_ID
WHERE (DATEDIFF(m, IC.certificationDate, GETDATE()) = 22 OR DATEDIFF(m, IC.certificationDate, GETDATE()) = 24 OR DATEDIFF(m, IC.certificationDate, GETDATE()) = 25) AND (C.classDate NOT BETWEEN IC.certificationDate AND GETDATE()) OR (C.classID NOT IN(SELECT classID
FROM Classes
WHERE I.instID = C.instID))

What would be the best way to detect the absence of a record in the child table?

Thanks

 

[bborissov]

Use LEFT JOIN not INNER, LEFT JOIN will get ALL records from you parent table and all matching records from your child table. Where there are no child records from the parent entry you will have NULL for these fields you select from child table:

--- To get all records from Parent table
--- which have no records in Child one

SELECT Parent.*
FROM Parent
LEFT JOIN Child ON Parent.PK = Child.FK
WHERE Child.FK IS NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[tis9700]

Thanks bborissov,
I still can't get it to work in one view but I got what I need by creating a separate view with the help of your statement.