How can I write this SQL better?

Question

I have the tables Patient, Service, PatientStatus, Status - a Patient can have multiple statuses discriminated by a Service.

I want to build a view that shows for each service and each patient what their current status is, even if they don't have a status for that service.

I've got some SQL that does this, but can I write it better? (I'm mainly concerned about the inner join on Patient with the 1 = 1)

Here's the SQL:

select
    p.Code,
    s.pkServiceId, 
    ps.fkPatientId, 
    ps.fkStatusId, 
    s.Code AS ServiceCode, 
    s.Description AS ServiceDescription, 
    st.Code AS StatusCode, 
    st.Description as StatusDescription, 
    ps.TsStart
from 
    Service s
inner join
    Patient p on 1 = 1
left outer join
      (select
      max(TsStart) AS TsStart, 
      fkPatientId, 
      fkServiceId
     from
      PatientStatus AS ps
     group by
      fkServiceId, fkPatientId
     ) AS psLast on 
     psLast.fkServiceId = s.pkServiceId and 
     psLast.fkPatientId = p.pkPatientId
left outer join
    PatientStatus AS ps ON 
     psLast.TsStart = ps.TsStart and 
     psLast.fkPatientId = ps.fkPatientId and 
     psLast.fkServiceId = ps.fkServiceId 
left outer join
    Status st on
     st.pkStatusId = ps.fkStatusId

Answer 1

Duh... my 1 = 1 is the same as rewriting it as a CROSS JOIN:

from Service s cross join Patient p

Answer 2

I've wondered why there is no relationship between Patient and Service table? Why should use " 1 = 1 "?

Assuming that Patient-Service is many to many relationship ... Service s inner join (PatientService ps inner join Patient p on ps.PCode = p.Code) on s.Code = ps.SCode ...

Answer 3

Is there a reason that you need the rows to show up for Patient/Services if they don't have a status for that service? It seems like this should be something handled on the front-end to me.

That said, to get what you're looking for I'd probably use the following:

SELECT
    P.Code,
    S.pkServiceID,  --Ugh, I hate that naming convention
    PS.fkPatientID,
    PS.fkStatusID,
    S.Code AS ServiceCode,
    S.Description AS ServiceDescription,
    ST.Code AS StatusCode,
    ST.Description AS StatusDescription
    PS.TsStart
FROM
    Patient P
CROSS JOIN Service S
LEFT OUTER JOIN PatientStatus PS ON
    PS.fkPatientID = P.pkPatientID AND
    PS.fkServiceID = S.pkServiceID
LEFT OUTER JOIN PatientStatusPS2 ON
    PS2.fkPatientID = P.pkPatientID AND
    PS2.fkServiceID = S.pkServiceID AND
    PS2.TsStart > PS.TsStart
LEFT OUTER JOIN Status ST ON
    ST.pkStatusID = PS.fkStatusID
WHERE
    PS2.fkPatientID IS NULL

Just a quick note... if you have two statuses with the exact same TsStart for the same patient and service then you will get duplicates here. You would get those from your original query as well though. You can code for that if needed. Just change the last line in the join on PS2 to:

(PS2.TsStart > PS.TsStart OR (PS2.TsStart = PS.TsStart AND PS2.pkID > PS.pkID))