Relationship of 3 fields

Question

I have 2 tables:

Table: PatientsUpdates

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?

please don't use picture to show the tables, if you can use the proper table markdown which can be used currently in SO :D — T. Peter, Jan 06 '21 at 01:35
[how-to-edit](https://stackoverflow.com/editing-help#tables) here is a link about how to do it. — T. Peter, Jan 06 '21 at 01:36
Why do you have those `CASE WHEN IS NOT NULL` all over the place? Is that sample data you show not representative? Are these not `NOT NULL` columns, and - for the TextXXX columns - a foreign key to TextMaster? — Mark Rotteveel, Jan 06 '21 at 07:38
Dear @Mark Rotteveel, Yes, that is sample data. Only some columns in table, namely PatientsUpdates, are NULL. I got the idea from [this question](https://dba.stackexchange.com/q/279568/218485). And, yes, TextXXX columns - a foreign key to TextMaster. — Nukool, Jan 06 '21 at 08:17
It is important to know which fields can be null, with a representative population, because that influences the answer. However, if the `TextXXX` columns are a foreign key, then they are populated together (or all null). — Mark Rotteveel, Jan 06 '21 at 08:24

score 0 · Accepted Answer · answered Jan 06 '21 at 08:33

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.

Relationship of 3 fields

Table: PatientsUpdates

Table: TextMaster

1 Answers1