Joining to the same table multiple times to retrieve different data each time using a filter

Question

I have a query which looks something like this (the actual table and column names are MUCH longer, it's a mess, and there are actually SIX fields I need to retrieve from the FieldSummary table but I haven't written out the whole query yet!)

select
    rh.RequirementDate,
    rt.RequirementType,
    f1s.Summary as F1,
    f2s.Summary as F2,
    f3s.Summary as F3
from
    RequirementHistory rh
    join RequirementTypeLU rt on rh.RequirementTypeLUID = rt.RequirementTypeLUID

    left join FieldSummary fs1 on rh.RequirementHistoryID = fs1.RequirementHistoryID -- all these identical joins, there's gotta be a better way...
    left join Field f1 on fs1.FieldID = f1.FieldID

    left join FieldSummary fs2 on rh.RequirementHistoryID = fs2.RequirementHistoryID
    left join Field f2 on fs2.FieldID = f2.FieldID

    left join FieldSummary fs3 on rh.RequirementHistoryID = fs3.RequirementHistoryID
    left join Field f3 on fs3.FieldID = f3.FieldID
where
    rh.LinkedEntityID = 3
    and f1.ScreenDescription = 'Field 1' -- this ScreenDescription lookup looks really brittle...
    and f2.ScreenDescription = 'Field 2'
    and f3.ScreenDescription = 'Field 3'

So I'm taking the RequirementHistory and RequirementTypeLU (lookup) tables, and joining them to the FieldSummary (actually data entered in the fields, not sure why it's called a summary) and Field (the field definitions) tables to retrieve the field data, once for each field (the actual field names are specified in the where clause as ScreenDescription). It's some sort of weird modular thing where system administrators can define their own "additional fields" to link to whatever entity types they want in the system. I know it looks horrible, I didn't design it!

Now my problem is I'm not getting any results. I'm sure something is wrong with my joins, but I'm not sure what. I also tried placing the ScreenDescription comparisons in the joins to Field, but that resulted in a ridiculous number of duplicate results, expanding with each pair of joins I added! What can I do to make this work properly? Ideally I want something like this as my results:

RequirementDate  RequirementType    F1    F2   F3
10/07/1983       Someone's birthday some  data here
09/11/2001       A disaster         more  data here
01/20/2021       Recovery! At last! still more data      

Where RequirementDate and RequirementType are pulled from their respective tables, and F1-3 are pulled from the FieldSummary table by looking up the appropriate field from Field using the ScreenDescription supplied in the where clause.

I know this is a huge mess but I'm trying to do my best with the database structure I have!

Answer 1

@TheImpaler's comments gave the correct hint to the answer - this is fleshing out the issues.

Issue 1: WHERE clause filtering

(Note that this becomes somewhat irrelevant with Issue 2 solution below. However, it's good to understand what's happening here as it is the thing blocking your current attempt.)

Just looking at the FROM Clause, you have, say

    left join FieldSummary fs1 on rh.RequirementHistoryID = fs1.RequirementHistoryID
    left join Field f1 on fs1.FieldID = f1.FieldID

and then in the WHERE clause you have

    and f1.ScreenDescription = 'Field 1'

The issue is that the query will do the LEFT JOIN onto fs1 and f1, and as a LEFT JOIN it will have NULLs in fields that don't match. However, you then filter by a field in F1 which then excludes those rows with NULLs. It effectively changes the LEFT JOINs into INNER JOINs.

Instead, you need to move the WHERE clauses on LEFT JOINed tables to be part of the join e.g.,

    left join FieldSummary fs1 on rh.RequirementHistoryID = fs1.RequirementHistoryID
    left join Field f1 on fs1.FieldID = f1.FieldID
                       and f1.ScreenDescription = 'Field 1'

Note that the above will report values from fs1 even if f1 doesn't match, as they're LEFT JOINed.

If you only want the rows from fs1 that also match the filtering on f1, then you need something like the following where both fs1 and f1 are filtering by f1's description.

    left join 
        (FieldSummary fs1 
            INNER join Field f1 on fs1.FieldID = f1.FieldID
                                and f1.ScreenDescription = 'Field 1'
        ) on rh.RequirementHistoryID = fs1.RequirementHistoryID

---

Issue 2: 'There's gotta be a better way'

There is - also included as one of @TheImpaler's comments.

You don't actually need all those joins - you only need to join them once e.g.,

select
    rh.RequirementDate,
    rt.RequirementType,
    fs.Summary,
    f.ScreenDescription
from
    RequirementHistory rh
    join RequirementTypeLU rt on rh.RequirementTypeLUID = rt.RequirementTypeLUID
    join FieldSummary fs on rh.RequirementHistoryID = fs.RequirementHistoryID
    join Field f on fs.FieldID = f.FieldId

That will give you a table with (say) up to 6 rows per rh/rt combination, where each row includes the FieldSummary and Field 'ScreenDescription'.

From there, you just need to convert the rows to columns - via a PIVOT command, or MAX(CASE()), or similar. Search for these e.g.,

• https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15
• https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/
• Convert Rows to columns using 'Pivot' in SQL Server
• Efficiently convert rows to columns in sql server

Answer 2

Use CASE logic to conditionally select the 'Summary' column values based on 'ScreenDescription'. Also, there were some minor issues with the table aliases being inconsistently applied which I corrected. Something like this.

select
    rh.RequirementDate,
    rt.RequirementType,
    case when f1.ScreenDescription='Field 1' then fs1.Summary else null end as F1,
    case when f1.ScreenDescription='Field 2' then fs1.Summary else null end as F2,
    case when f1.ScreenDescription='Field 3' then fs1.Summary else null end as F3
from
    RequirementHistory rh
    join RequirementTypeLU rt on rh.RequirementTypeLUID = rt.RequirementTypeLUID
    left join FieldSummary fs1 on rh.RequirementHistoryID = fs1.RequirementHistoryID
    left join Field f1 on fs1.FieldID = f1.FieldID
where
    rh.LinkedEntityID=3;