Patients with Primary Provider and Primary Dx, Sorted by Provider Code

SELECT
   Patients.LastName,
   Patients.FirstName,
   Patients.ID,
 
   IF Patients.ProviderNum IS NULL
     THEN 'None'
     ELSE (Providers.ProvCode +': '+ Providers.ProvLName +', ' + Providers.ProvFName)
   END IF AS "Provider",
 
   IF Dx.DxCode IS NULL
     THEN 'None'
     ELSE Dx.DxCode
   END IF AS "PrimaryDx"
FROM
   Patients
   LEFT OUTER JOIN Providers ON Patients.ProviderNum = Providers.ProviderNum
   LEFT OUTER JOIN PtCSU ON Patients.PtNum = PtCSU.PtNum
LEFT OUTER JOIN Dx ON PtCSU.Dx1 = Dx.DxNum
WHERE
   Patients.LicNum = 101
   AND Patients.Flag = 0
   AND Patients.DischargeDate IS NULL
   AND PtCSU.TypeFlag = 'D'
ORDER BY
   Providers.ProvCode,Patients.LastName,Patients.FirstName,Patients.id
; OUTPUT TO c:\sos\provptdxlist.html FORMAT HTML