Search code examples
sqlfirebirdfirebird-3.0

Relationship of 3 fields

I have 2 tables:

Table: PatientsUpdates

Table: PatientsUpdates

Table: TextMaster

Table: TextMaster

I need the final result:

Current status

The final result is the current status of all patients of the gender reassignment surgery section in a hospital, but title of a patient's name is selected from TextMaster table.

From this SQL:

SELECT DISTINCT "PatientID",                    
  FIRST_VALUE("TextSource") 
    OVER (PARTITION BY "PatientID" ORDER BY CASE WHEN "TextSource" IS NOT NULL THEN "EffectiveDate" END DESC) "TextSource",
  FIRST_VALUE("TextGroup") 
    OVER (PARTITION BY "PatientID" ORDER BY CASE WHEN "TextGroup" IS NOT NULL THEN "EffectiveDate" END DESC) "TextGroup",
  FIRST_VALUE("TextIndex") 
    OVER (PARTITION BY "PatientID" ORDER BY CASE WHEN "TextIndex" IS NOT NULL THEN "EffectiveDate" END DESC) "TextIndex",
  FIRST_VALUE("FirstName") 
    OVER (PARTITION BY "PatientID" ORDER BY CASE WHEN "FirstName" IS NOT NULL THEN "EffectiveDate" END DESC) "FirstName",
  FIRST_VALUE("LastName") 
    OVER (PARTITION BY "PatientID" ORDER BY CASE WHEN "LastName" IS NOT NULL THEN "EffectiveDate" END DESC) "LastName"
FROM "PatientsUpdates";

I have got this query result:

Firebird query

Can you please help teach me with the correct SQL to replace TextSource, TextGroup and TextIndex with TextDetail or better SQL than as shown above?

Solution

  • If the TextXXX are part of a foreign key to "TextMaster", then for a single row, they are either all null, or populated together. In that case, you can simply left join to "TextMaster" to get the information.

    SELECT DISTINCT pu."PatientID",
  FIRST_VALUE(tm."TextDetail") 
    OVER (PARTITION BY pu."PatientID" ORDER BY CASE WHEN "TextDetail" IS NOT NULL THEN "EffectiveDate" END DESC) "TextDetail",
  FIRST_VALUE(pu."FirstName") 
    OVER (PARTITION BY pu."PatientID" ORDER BY CASE WHEN pu."FirstName" IS NOT NULL THEN "EffectiveDate" END DESC) "FirstName",
  FIRST_VALUE(pu."LastName") 
    OVER (PARTITION BY pu."PatientID" ORDER BY CASE WHEN pu."LastName" IS NOT NULL THEN "EffectiveDate" END DESC) "LastName"
FROM "PatientsUpdates" pu
left join "TextMaster" tm
  using ("TextSource", "TextGroup", "TextIndex");

    I'm using LEFT JOIN instead of INNER JOIN because of the lack of information in your question what can be null or not and in what combinations, so to avoid accidentally eliminating rows that would provide necessary information. However, if "TextSource", "TextGroup" and "TextIndex" are always populated, or always populated when "FirstName" and/or "LastName" are also populated, then you could use an INNER JOIN instead.