SQL database - joining, but with nulls

Question

I'm having trouble with the following view (using SQL Server 2008). Basically, I've got three tables - tbl_PhysicalAsset (PA), tbl_Operations (OP) and tbl_Schedules (SC). One record in PA relates to many in OP, and one in OP to many in SC. The PA and OP are irrelevant here really, but the SC one is giving me problems.

I'm writing a query which displays the maximum done date from SC (basically done by grouping on the field which relates back to the OP, and the max of the done date), which is fair enough. I've also had to try and get the "Issues" column (a varchar) from the SC table too, in case there were issues. This is where it's falling down - some of the things in SC will not have a "done date" filled in (ie it will be null), but we still need to display the information.

The following is where I've got to:

SELECT TOP (100) PERCENT 
   dbo.tbl_PhysicalAsset.FKID_Contract, dbo.tbl_PhysicalAsset.MyLevel, 
   dbo.tbl_PhysicalAsset.L1_Name, dbo.tbl_PhysicalAsset.L2_Name, 
   dbo.tbl_PhysicalAsset.L3_Name, s1.FKID_Operation, 
   dbo.tbl_PhysicalAsset.Deleted AS Del1, 
   dbo.tbl_Operations.Deleted AS Del2, s1.Deleted AS Del3, 
   s1.SchedDone, s1.Issues
FROM         
   dbo.tbl_Schedules AS s1 
INNER JOIN
   dbo.tbl_Operations 
INNER JOIN
   dbo.tbl_PhysicalAsset ON dbo.tbl_Operations.FKID_PhysicalAsset = dbo.tbl_PhysicalAsset.PKID_PhysicalAsset 
INNER JOIN
   (SELECT     
       MAX(SchedDone) AS SchedDone, FKID_Operation
    FROM 
       dbo.tbl_Schedules
    GROUP BY 
       FKID_Operation) AS s2 ON dbo.tbl_Operations.PKID_Operation = s2.FKID_Operation ON s1.FKID_Operation = s2.FKID_Operation AND s1.SchedDone = s2.SchedDone
WHERE     
    (dbo.tbl_PhysicalAsset.FKID_Contract = 6) 
    AND (dbo.tbl_PhysicalAsset.MyLevel = 3) 
    AND (s1.FKID_Operation IS NOT NULL) 
    AND (dbo.tbl_PhysicalAsset.Deleted = 0) 
    AND (dbo.tbl_Operations.Deleted = 0) 
    AND (s1.Deleted = 0)
ORDER BY 
    dbo.tbl_PhysicalAsset.L1_Name, dbo.tbl_PhysicalAsset.L2_Name, 
    dbo.tbl_PhysicalAsset.L3_Name

As I say, if a record is missing a max done date then all information regarding it doesn't appear.

I've tried using left joins (and even right joins) but this makes no difference (and SQL Server normally tries to be helpful and makes them outer joins).

Answer 1

Okay, this was my bad. I made a mistake with some of my deleted fields. Turns out that the original solution was pretty much spot-on and I was looking for anything that contained a False in the Deleted column (in my case, s1). However, I hadn't thought that, if a record can't be found, it will of course not have a False. So I moved which table I was checking for the Deleted schedules to s2 and just used the Schedules table:

SELECT TOP (100) PERCENT dbo.tbl_PhysicalAsset.FKID_Contract, dbo.tbl_PhysicalAsset.MyLevel, dbo.tbl_PhysicalAsset.L1_Name, dbo.tbl_PhysicalAsset.L2_Name, 
                  dbo.tbl_PhysicalAsset.L3_Name, dbo.tbl_PhysicalAsset.Deleted AS Del1, dbo.tbl_Operations.Deleted AS Del2, s2.SchedDone, s2.Deleted, tbl_Schedules_1.Issues, 
                  tbl_Schedules_1.Deleted AS Expr1
FROM dbo.tbl_Operations INNER JOIN
                  dbo.tbl_PhysicalAsset ON dbo.tbl_Operations.FKID_PhysicalAsset = dbo.tbl_PhysicalAsset.PKID_PhysicalAsset INNER JOIN
                      (SELECT     MAX(SchedDone) AS SchedDone, FKID_Operation, Deleted
                        FROM          dbo.tbl_Schedules
                        GROUP BY FKID_Operation, Deleted) AS s2 ON dbo.tbl_Operations.PKID_Operation = s2.FKID_Operation LEFT OUTER JOIN
                  dbo.tbl_Schedules AS tbl_Schedules_1 ON s2.SchedDone = tbl_Schedules_1.SchedDone AND s2.FKID_Operation = tbl_Schedules_1.FKID_Operation
WHERE (dbo.tbl_PhysicalAsset.FKID_Contract = 6) AND (dbo.tbl_PhysicalAsset.MyLevel = 3) AND (dbo.tbl_PhysicalAsset.Deleted = 0) AND (dbo.tbl_Operations.Deleted = 0) 
                  AND (s2.Deleted = 0)
ORDER BY dbo.tbl_PhysicalAsset.L1_Name, dbo.tbl_PhysicalAsset.L2_Name, dbo.tbl_PhysicalAsset.L3_Name

Answer 2

try now ....

SELECT PA.FKID_Contract
    , PA.MyLevel
    , PA.L1_Name
    , PA.L2_Name
    , PA.L3_Name
    , s1.FKID_Operation
    , PA.Deleted AS Del1
    , OPS.Deleted AS Del2
    , s1.Deleted AS Del3
    , s1.SchedDone
    , s1.Issues
FROM dbo.tbl_Schedules AS s1 
INNER JOIN dbo.tbl_Operations    OPS ON OPS.PKID_Operation = s1.FKID_Operation 
INNER JOIN dbo.tbl_PhysicalAsset PA  ON OPS.FKID_PhysicalAsset = PA.PKID_PhysicalAsset 
INNER JOIN 
           (SELECT  MAX(SchedDone) AS SchedDone, FKID_Operation
           FROM     dbo.tbl_Schedules
           GROUP BY FKID_Operation) AS s2 
           ON  s1.FKID_Operation = s2.FKID_Operation 
           AND s1.SchedDone = s2.SchedDone
WHERE (PA.FKID_Contract = 6) 
AND   (PA.MyLevel = 3) 
AND   (s1.FKID_Operation IS NOT NULL) 
AND   (PA.Deleted = 0) 
AND   (OPS.Deleted = 0) 
AND    (s1.Deleted = 0)
ORDER BY PA.L1_Name, PA.L2_Name, PA.L3_Name