Where to put the where clause for Nulls (1 Viewer)

[Zydeceltico]

Hi All -

I have the following code that serves as the Record Source for a report:

SELECT tblinspectionevent.datetime,
       [jobnumber] & [task] & [resource] AS Job,
       Operator.firstname                AS Operator,
       tblinspectionevent.bundlenumber,
       tblinspectionevent.piecemark,
       tblparts.parttype,
       tblworkstations.workstation,
       tblcoils.coilnumber,
       tblcoils.supplier,
       tblinspectionnotes.inspectionnotes
FROM   tblworkstations
       INNER JOIN (tbljobs
                   INNER JOIN (((lutblpersonnel AS Operator
                                 INNER JOIN tblinspectionevent
                                         ON Operator.personnel_pk =
                                tblinspectionevent.operator_fk)
                                INNER JOIN tblinspectionnotes
                                        ON tblinspectionevent.inspectionevent_pk
                                           =
tblinspectionnotes.inspectionevent_fk)
INNER JOIN (tblcoils
INNER JOIN (tblparts
            INNER JOIN tblinspectmill
                    ON tblparts.part_id =
           tblinspectmill.parttype_fk)
        ON tblcoils.coilnumber_pk =
           tblinspectmill.[coilnumber_pk])
ON tblinspectionevent.inspectionevent_pk =
tblinspectmill.inspectionevent_fk)
ON tbljobs.job_id = tblinspectionevent.job_fk)
ON tblworkstations.workstation_id = tblparts.workstation_fk
ORDER  BY tblinspectionevent.datetime;

I think the query is not returning records where the inspections notes field are empty whether that be an empty string or null.

What do I have to add to this SQL to get the query to return ALL records regardless of whether a field has a value, an empty string, or is null?

Thanks,

Tim

 

[plog]

Since you are using nothing but INNER JOINs, there must be matching data in all your joined tables. If you want to include records regardless of matches in a downstream table, then you should use LEFT JOIN:

...FROM A INNER JOIN B ON A.field1 = B.field1 LEFT JOIN C ON B.field2 = C.field2 ...

If a record is in A it must have a match in B to show in the results. If there is a match for a record in A and B but not in C the record still shows because of the LEFT JOIN, the C values just appear blank.