0

Need to use same lookup table to fetch the description for multiple columns. Is there any way to JOIN with lookup tables only once? I need to reduce following multiple joins

SELECT RV.Description AS PAY_STATUS,
    RV1.Description AS CALC_STATUS,
    RV2.Description AS RES_STATUS from PARTICIPANTS pp
LEFT JOIN [dbo].[LOOKUP_VALUE] RV ON RV.VALUE = pp.PAY_STATUS AND RV.IS_ACTIVE='Y' AND RV.LOOKUP_TYPE_ID in (
            select RLT.ID FROM [dbo].[LOOKUP_Type] RLT WHERE RLT.IS_ACTIVE='Y' AND RLT.LOOKUP_TYPE='PAY_FILE_STATUS')
LEFT JOIN [dbo].[LOOKUP_VALUE] RV2 ON RV2.VALUE = pp.CALC_STATUS AND RV2.IS_ACTIVE='Y' AND RV2.LOOKUP_TYPE_ID in (
            select RLT.ID FROM [dbo].[LOOKUP_Type] RLT WHERE RLT.IS_ACTIVE='Y' AND RLT.LOOKUP_TYPE='CALC_FILE_STATUS')
LEFT JOIN [dbo].[LOOKUP_VALUE] RV3 ON RV.VALUE = pp.RES_STATUS AND RV3.IS_ACTIVE='Y' AND RV3.LOOKUP_TYPE_ID in (
        select RLT.ID FROM [dbo].[LOOKUP_Type] RLT WHERE RLT.IS_ACTIVE='Y' AND RLT.LOOKUP_TYPE='RES_FILE_STATUS')
VMK
  • 35
  • 1
  • 6
  • if that's a possibility you should define a [database view to simplify and optimize the query](https://stackoverflow.com/a/1278620/14807735) – mimak May 12 '23 at 12:08
  • What you have is correct. Although I would say that the whole design is problematic: it's called EAV design and is a known anti-pattern. – Charlieface May 12 '23 at 12:22
  • @mimak Not sure why you think a view is more optimized – Charlieface May 12 '23 at 12:22
  • @Charlieface i meant indexed views, you can have a read [here](https://stackoverflow.com/a/439061/14807735) – mimak May 12 '23 at 12:48

0 Answers0