I need to write an SQL query to get data from a DB where the software suite allows users to create their own custom fields.
Here are the DB tables:
FileHdr
FileID (PK)
NameFile
OpenfileNo
CustomTplM
CustomTplMID (PK)
NameCustomTplM
**CustomTplD **
CustomTplDID (PK)
CustomTplMID (references CustomTplM.CustomTplMID)
OrderID
PromptText
CustomDataM
CustomDataMID (PK)
CustomTplMID (references CustomTplM.CustomTplMID)
NameCustomDataM
EntityID (references FileHdr.FileID)
CustomDataD
CustomDataDID (PK)
CustomDataMID (references CustomDataM.CustomDataMID)
CustomTplMID
CustomTplDID
PromptText
ValueDate
ValueEntry
The CustomTplM and CustomTplD tables represent templates that hold the names of the custom fields.
The CustomDataM and CustomDataD tables contain the values for the custom fields.
Here is my query:
select FileID, NameFile, OpenFileNo, tm.CustomTplMID, tm.NameCustomTplM, dm.CustomDataMID, dm.NameCustomDataM, td.OrderID, td.PromptText, dd.ValueDate, dd.ValueEntry from FileHdr fh
left outer join CustomDataM dm
on fh.FileID = dm.EntityID
left outer join CustomDataD dd
on dm.CustomDataMID = dd.CustomDataMID
left outer join CustomTplM tm
on dm.CustomTplMID = tm.CustomTplMID
inner join CustomTplD td
on tm.CustomTplMID = td.CustomTplMID
where OpenFileNo = 'RR-2737'
--order by td.OrderID
When I join the FielHdr table to the template tables, my result set is correct. When I join FileHdr to the data tables, my result set is correct. However, when I join the template and data tables to match up the custom fields with their data, I get some sort of "cross" join and end of with thousands of redundant records. I have tried both inner and outer joins.
How can I query data with this type of structure? I can provide sample data if needed. Thanks.