How to create a result set from a select query using multiple joins AND pull values from unlinked table where certain values exist

Question

I have a table which contains values for a collection of data. The value field thus contains multiple types of data - dates, numbers, text. One of the fields in the collection is a lookup to another unlinked table.

I can figure out the select for the collection but not how to also pull in the value from the unlinked table.

SELECT DISTINCT CFV.Value, CFV.CustomRecordsetId, CFV.CustomFieldId, 
                CF.Name, F.id, F.ShortFileName, F.DateFileOpen
FROM [DB].[dbo].[CustomFieldValue] CFV
        inner join [DB].[dbo].[CustomField] CF on cfv.customfieldId=cf.id
        inner join [DB].[dbo].[CustomRecordset] CRS on CRS.Id=CFV.CustomRecordsetId
        inner join [DB].[dbo].[File] F on F.Id=CRS.RecordId                              
WHERE F.Id = 27519
 AND  CF.Id = CFV.CustomFieldId
 AND  CF.CustomFieldTypeId <> 23 
 AND  CRS.customrecordid = 8
 AND (cf.name = 'MedLienAcctNo' 
       OR cf.name = 'MedLienAmountBilled1' OR cf.name = 'MedLienAmt' 
       OR cf.name = 'MedLienAmtDue' OR cf.name = 'MedLienDateRec' 
       OR cf.name = 'MedLienDiagAmt' OR cf.name = 'MedLienListTF1' 
       OR cf.name = 'MedLienLKUPMedProvider1' OR cf.name = 'MedLienNotes' 
       OR cf.name = 'MedLienServFromDate1' 
       OR cf.name = 'MedLienServToDate1' 
       OR cf.name = 'MedLienTreatAmt')

I need to be able to pull out the data for only the 'MedLienLKUPMedProvider1' and lookup a name field in an unlinked People table, i.e. People.Name where 'MedLienLKUPMedProvider1'=People.Id

Answer 1

SELECT DISTINCT CFV.Value, CFV.CustomRecordsetId, CFV.CustomFieldId, 
                CF.Name, F.id, F.ShortFileName, F.DateFileOpen,
                p.SomeFieldName
FROM [DB].[dbo].[CustomFieldValue] CFV
        inner join [DB].[dbo].[CustomField] CF on cfv.customfieldId=cf.id
        inner join [DB].[dbo].[CustomRecordset] CRS on CRS.Id=CFV.CustomRecordsetId
        inner join [DB].[dbo].[File] F on F.Id=CRS.RecordId
        left outer join [DB].[dbo].[People] p on cf.name = p.Id and cf.name = 'MedLienLKUPMedProvider1'
WHERE F.Id = 27519
 AND  CF.Id = CFV.CustomFieldId
 AND  CF.CustomFieldTypeId <> 23 
 AND  CRS.customrecordid = 8
 AND  cf.name IN ('MedLienAcctNo', 'MedLienAmountBilled1', 'MedLienAmt', 'MedLienAmtDue', 'MedLienDateRec',
      'MedLienDiagAmt', 'MedLienListTF1', 'MedLienLKUPMedProvider1', 'MedLienNotes', 'MedLienServFromDate1',
      'MedLienServToDate1', 'MedLienTreatAmt')

Does that do what you want? It's hard to tell when you mean by "unlinked People table" when it looks like there's a link between the value in cf.name and the People.Id field.

That "cf.name in (...)" restriction is pretty sucky. It looks like you should put that information into another table that expresses the relationship of your data. Perhaps something like CfName varchar(50), Category varchar(50). If you had something like that then you can link your query with that table and make a useful restriction that explains what your query is all about such as " where OtherTable.Category = 'Medical Finances'" This also means that when there's one more type of category that should be included in your result set, go insert one row in a table instead of modifying your code. You're probably likely to use this data set restriction elsewhere in your database so your one row update might save you lots of code updates.

Like JNK said, "generic" fields are a bad idea for lots of reasons. A table design that meaningfully expresses what the data is that you're managing will save you from a lot of trouble down the road.

Answer 2

Thanks. I used a subquery in the FROM that worked but it only allows one field to pull so this will probably work a lot better. I did try an inner join but kept getting an error about not being able to convert a varchar into an int with value 2010-01-01 (example) so I gave up and went to the subquery. Used same code but it worked. I'll try yours and post the results.

Unfortunately, it's a third party COTS DB that is used by a client so I can't modify the table structures. It also won't let me create temp tables from Crystal at report time.

I appreciate the feedback and the solution.