Change Cross Apply to Left Outer Join 1

[Auguy]

I have this procedure which works well. My issue is I also want the records where the the cross apply doesn't find anything. I tried to re-write as a left outer join but can't seem to get it right. Can somebody point me in the right direction? What I am looking for is the current PlateNbr information and the most recent previous PlateNBr information based on the DateOn. But there may not be a previous entry.

select UP.PlateNbr, UP.UnitMasterFK, ds.PlateNbr, ds.UnitMasterFK, ds.Unit
 from unitplate UP
 cross apply 
     (select top 1 UP2.PlateNbr,  UP2.UnitMasterFK, Um.Unit
      from unitplate UP2
	  INNER JOIN UnitMaster UM ON UP2.UnitMasterFK = UM.UnitMasterPK
      where UP2.PlateNbr = UP.PlateNbr and UP2.UnitPlatePK <> UP.UnitPlatePK
      order by DateOn desc) ds

where UP.daterequest > '20180328' and UP.PlateNbr='488444'

Auguy
Sylvania/Toledo Ohio

 

[DaveInIowa]

Use OUTER APPLY instead; it's the equivalent of a LEFT JOIN.

 

[Auguy]

Thanks Dave! I completely forgot about Outer Apply. Probably should be using both of them more. I will have to do some studying on them.

Auguy
Sylvania/Toledo Ohio