Join to lookup table on multiple columns

  • Hi I have a table Meter and a lookup table LookupTbl I need to get the description for multiple fields by joining with the lookup table on multiple columns.

    Can anyone suggest a good way of doing this, without having multiple joins as below:

     

    select t.*, s.Description as SubTypeCD_DESC, d1.Description asAOD_METHOD_DESC, d2.Description as OWNER_DESC, d3.Description asID_DMA_CODE_DESC,d4.Description as ID_CONTROL_GROUP_DESC, d5.Description asOPERATIONAL_STATUS_DESC, d6.Description as SOURCE_METHOD_DESC, d7.Descriptionas QA_STATUS_DESC,d8.Description as SECURITY_IND_DESC, d9.Description as Enabled_DESC,d10.Description as AncillaryRole_DESC from CGIdb.CGI1.Meter tleft join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) andupper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')left join CGIdb.CGI1.[LookUpTbl2] d1 ON upper(t.AOD_METHOD)=upper(d1.Code)and upper(d1.Field) =upper('AOD_METHOD')left join CGIdb.CGI1.[LookUpTbl2] d2 ON upper(t.OWNER)=upper(d2.Code) andupper(d2.Field) =upper('OWNER')left join CGIdb.CGI1.[LookUpTbl2] d3 ON upper(t.ID_DMA_CODE)=upper(d3.Code)and upper(d3.Field) =upper('ID_DMA_CODE')left join CGIdb.CGI1.[LookUpTbl2] d4 ONupper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field)=upper('ID_CONTROL_GROUP')left join CGIdb.CGI1.[LookUpTbl2] d5 ONupper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field)=upper('OPERATIONAL_STATUS')left join CGIdb.CGI1.[LookUpTbl2] d6 ON upper(t.SOURCE_METHOD)=upper(d6.Code)and upper(d6.Field) =upper('SOURCE_METHOD')left join CGIdb.CGI1.[LookUpTbl2] d7 ON upper(t.QA_STATUS)=upper(d7.Code) andupper(d7.Field) =upper('QA_STATUS')left join CGIdb.CGI1.[LookUpTbl2] d8 ON upper(t.SECURITY_IND)=upper(d8.Code)and upper(d8.Field) =upper('SECURITY_IND')left join CGIdb.CGI1.[LookUpTbl2] d9 ON upper(t.Enabled)=upper(d9.Code) andupper(d9.Field) =upper('Enabled')left join CGIdb.CGI1.[LookUpTbl2] d10 ONupper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field)=upper('AncillaryRole')
  • I don't think that is going to be possible - each of the different values you are looking up will exist on a different row and there is nothing that relates any of the lookup rows to another lookup row.  You cannot pivot the data into a single row and lookup based on a single code so you must perform the lookup individually.

    With that said...if you are trying to improve performance then remove the 'upper' statements.  If this is a case-sensitive collation, then make sure the values in the 'Meter' table and the values in the lookup table are set with the correct case - and make sure the values you input into the statement are of the correct case.

    If this is not a case-sensitive collation then you don't need to perform that check at all.

    In other words...if the value in the lookup table for the column 'Tablename' is 'GISSTdbR.STWGIS1.cwFitting' (exactly this) - then in a case-sensitive collation this will work s.Tablename = 'GISSTdbR.STWGIS1.cwFitting' - and in a case-insensitive collation then this would work s.Tablename = 'GISSTdbR.STWGIS1.CWFITTING' (or all lowercase, mixed case, etc...).

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  •  

     

     

    this looks like code converted from Oracle - is your database/tables really setup to be case sensitive that you require to use UPPER for every single column?

    there is a way - but not pretty and not necessarily better from a performance point of view - not that it matters here as these upper functions are killing your performance in any case.

    code formatted so it can be read by others

    select t.*
    , s.Description as SubTypeCD_DESC
    , d1.Description asAOD_METHOD_DESC
    , d2.Description as OWNER_DESC
    , d3.Description asID_DMA_CODE_DESC
    , d4.Description as ID_CONTROL_GROUP_DESC
    , d5.Description asOPERATIONAL_STATUS_DESC
    , d6.Description as SOURCE_METHOD_DESC
    , d7.Descriptionas QA_STATUS_DESC
    , d8.Description as SECURITY_IND_DESC
    , d9.Description as Enabled_DESC
    , d10.Description as AncillaryRole_DESC
    from CGIdb.CGI1.Meter t
    Left join CGIdb.CGI1.[LookUpTbl1] s
    On upper(subtypecd) = upper(s.Code)
    and upper(s.Tablename) = upper('GISSTdbR.STWGIS1.cwFitting')
    Left join CGIdb.CGI1.[LookUpTbl2] d1
    On upper(t.AOD_METHOD) = upper(d1.Code)
    and upper(d1.Field) = upper('AOD_METHOD')
    Left join CGIdb.CGI1.[LookUpTbl2] d2
    On upper(t.OWNER) = upper(d2.Code)
    and upper(d2.Field) = upper('OWNER')
    Left join CGIdb.CGI1.[LookUpTbl2] d3
    On upper(t.ID_DMA_CODE) = upper(d3.Code)
    and upper(d3.Field) = upper('ID_DMA_CODE')
    Left join CGIdb.CGI1.[LookUpTbl2] d4
    On upper(t.ID_CONTROL_GROUP) = upper(d4.Code)
    and upper(d4.Field) = upper('ID_CONTROL_GROUP')
    Left join CGIdb.CGI1.[LookUpTbl2] d5
    On upper(t.OPERATIONAL_STATUS) = upper(d5.Code)
    and upper(d5.Field) = upper('OPERATIONAL_STATUS')
    Left join CGIdb.CGI1.[LookUpTbl2] d6
    On upper(t.SOURCE_METHOD) = upper(d6.Code)
    and upper(d6.Field) = upper('SOURCE_METHOD')
    Left join CGIdb.CGI1.[LookUpTbl2] d7
    On upper(t.QA_STATUS) = upper(d7.Code)
    and upper(d7.Field) = upper('QA_STATUS')
    Left join CGIdb.CGI1.[LookUpTbl2] d8
    On upper(t.SECURITY_IND) = upper(d8.Code)
    and upper(d8.Field) = upper('SECURITY_IND')
    Left join CGIdb.CGI1.[LookUpTbl2] d9
    On upper(t.Enabled) = upper(d9.Code)
    and upper(d9.Field) = upper('Enabled')
    Left join CGIdb.CGI1.[LookUpTbl2] d10
    On upper(t.AncillaryRole) = upper(d10.Code)
    and upper(d10.Field) = upper('AncillaryRole')

    possible way to do it - didn't add the upper functions but feel free to do it if really required by your db.

    select t.*
    , s.Description as SubTypeCD_DESC
    , dsc.*
    from CGIdb.CGI1.Meter t
    Left join CGIdb.CGI1.LookUpTbl1 s
    On upper(subtypecd) = upper(s.Code)
    and upper(s.Tablename) = upper('GISSTdbR.STWGIS1.cwFitting')
    outer apply (select max(case when t2.LookupCode = 'AOD_METHOD' then lk.Description else null end) as AOD_METHOD_DESC
    , max(case when t2.LookupCode = 'OWNER' then lk.Description else null end) as OWNER_DESC
    , max(case when t2.LookupCode = 'ID_DMA_CODE' then lk.Description else null end) as ID_DMA_CODE_DESC
    ....
    from (select t.AOD_Method as LookupValue, 'AOD_METHOD' as LookupCode
    union all
    select t.OWNER as LookupValue, 'OWNER' as LookupCode
    union all
    select t.ID_DMA_CODE as LookupValue, 'ID_DMA_CODE' as LookupCode
    ... repeat for remaining lookup values
    ) t2
    left join CGIdb.CGI1.LookUpTbl2 lk
    on lk.Field = t2.LookupCode
    and lk.Code = t2.LookupValue
    ) Dsc
  • Thanks for the replies. Not sure whether this makes a difference but I think I needed to also add:

    the joins on LookUpTbl2 is required only for those columns in Meter table which exist as row values in LookUpTbl2.

    i.e. if column name in Meter exists as value of column Field in LookUpTbl2, then join on code

     

  • You can use a single table scan / lookup and will often get better performance from doing so:

    select t.*, s.Description as SubTypeCD_DESC, 
    d.AOD_METHOD_DESC,
    d.OWNER_DESC,
    d.ID_DMA_CODE_DESC,
    d.ID_CONTROL_GROUP_DESC,
    d.OPERATIONAL_STATUS_DESC,
    d.SOURCE_METHOD_DESC,
    d.QA_STATUS_DESC,
    d.SECURITY_IND_DESC,
    d.Enabled_DESC,
    d.AncillaryRole_DESC
    from CGIdb.CGI1.Meter t
    left join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) and upper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')
    outer apply (
    select
    max(case when (upper(t.AOD_METHOD)=upper(d1.Code) and upper(d1.Field)=upper('AOD_METHOD'))
    then Description end) as AOD_METHOD_DESC,
    max(case when (upper(t.OWNER)=upper(d2.Code) and upper(d2.Field)=upper('OWNER'))
    then Description end) as OWNER_DESC,
    max(case when (upper(t.ID_DMA_CODE)=upper(d3.Code)and upper(d3.Field)=upper('ID_DMA_CODE'))
    then Description end) as ID_DMA_CODE_DESC,
    max(case when (upper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field)=upper('ID_CONTROL_GROUP'))
    then Description end) as ID_CONTROL_GROUP_DESC,
    max(case when (upper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field)=upper('OPERATIONAL_STATUS'))
    then Description end) as OPERATIONAL_STATUS_DESC,
    max(case when (upper(t.SOURCE_METHOD)=upper(d6.Code)and upper(d6.Field)=upper('SOURCE_METHOD'))
    then Description end) as SOURCE_METHOD_DESC,
    max(case when (upper(t.QA_STATUS)=upper(d7.Code) and upper(d7.Field)=upper('QA_STATUS'))
    then Description end) as QA_STATUS_DESC,
    max(case when (upper(t.SECURITY_IND)=upper(d8.Code)and upper(d8.Field)=upper('SECURITY_IND'))
    then Description end) as SECURITY_IND_DESC,
    max(case when (upper(t.Enabled)=upper(d9.Code) and upper(d9.Field)=upper('Enabled'))
    then Description end) as Enabled_DESC,
    max(case when (upper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field)=upper('AncillaryRole'))
    then Description end) as AncillaryRole_DESC
    from CGIdb.CGI1.[LookUpTbl2]
    where
    (upper(t.AOD_METHOD)=upper(d1.Code) and upper(d1.Field)=upper('AOD_METHOD')) or
    (upper(t.OWNER)=upper(d2.Code) and upper(d2.Field)=upper('OWNER')) or
    (upper(t.ID_DMA_CODE)=upper(d3.Code)and upper(d3.Field)=upper('ID_DMA_CODE')) or
    (upper(t.ID_CONTROL_GROUP)=upper(d4.Code) and upper(d4.Field)=upper('ID_CONTROL_GROUP')) or
    (upper(t.OPERATIONAL_STATUS)=upper(d5.Code) and upper(d5.Field)=upper('OPERATIONAL_STATUS')) or
    (upper(t.SOURCE_METHOD)=upper(d6.Code)and upper(d6.Field)=upper('SOURCE_METHOD')) or
    (upper(t.QA_STATUS)=upper(d7.Code) and upper(d7.Field)=upper('QA_STATUS')) or
    (upper(t.SECURITY_IND)=upper(d8.Code)and upper(d8.Field)=upper('SECURITY_IND')) or
    (upper(t.Enabled)=upper(d9.Code) and upper(d9.Field)=upper('Enabled')) or
    (upper(t.AncillaryRole)=upper(d10.Code) and upper(d10.Field)=upper('AncillaryRole'))
    ) as d

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Thanks. But the above sql doesn't quite work as d1, d2 etc.. are not bound to any table

  • Yeah, sorry, adjust all the "d." from the query.  You didn't provide any usable sample data for us to test with, so you can't expect fully tested code :-).

    select t.*, s.Description as SubTypeCD_DESC, 
    d.AOD_METHOD_DESC,
    d.OWNER_DESC,
    d.ID_DMA_CODE_DESC,
    d.ID_CONTROL_GROUP_DESC,
    d.OPERATIONAL_STATUS_DESC,
    d.SOURCE_METHOD_DESC,
    d.QA_STATUS_DESC,
    d.SECURITY_IND_DESC,
    d.Enabled_DESC,
    d.AncillaryRole_DESC
    from CGIdb.CGI1.Meter t
    left join CGIdb.CGI1.[LookUpTbl1] s ON upper(subtypecd)=upper(s.Code) and upper(s.Tablename) =upper('GISSTdbR.STWGIS1.cwFitting')
    outer apply (
    select
    max(case when (upper(t.AOD_METHOD)=upper(d.Code) and upper(d.Field)=upper('AOD_METHOD'))
    then Description end) as AOD_METHOD_DESC,
    max(case when (upper(t.OWNER)=upper(d.Code) and upper(d.Field)=upper('OWNER'))
    then Description end) as OWNER_DESC,
    max(case when (upper(t.ID_DMA_CODE)=upper(d.Code)and upper(d.Field)=upper('ID_DMA_CODE'))
    then Description end) as ID_DMA_CODE_DESC,
    max(case when (upper(t.ID_CONTROL_GROUP)=upper(d.Code) and upper(d.Field)=upper('ID_CONTROL_GROUP'))
    then Description end) as ID_CONTROL_GROUP_DESC,
    max(case when (upper(t.OPERATIONAL_STATUS)=upper(d.Code) and upper(d.Field)=upper('OPERATIONAL_STATUS'))
    then Description end) as OPERATIONAL_STATUS_DESC,
    max(case when (upper(t.SOURCE_METHOD)=upper(d.Code)and upper(d.Field)=upper('SOURCE_METHOD'))
    then Description end) as SOURCE_METHOD_DESC,
    max(case when (upper(t.QA_STATUS)=upper(d.Code) and upper(d.Field)=upper('QA_STATUS'))
    then Description end) as QA_STATUS_DESC,
    max(case when (upper(t.SECURITY_IND)=upper(d.Code)and upper(d.Field)=upper('SECURITY_IND'))
    then Description end) as SECURITY_IND_DESC,
    max(case when (upper(t.Enabled)=upper(d.Code) and upper(d.Field)=upper('Enabled'))
    then Description end) as Enabled_DESC,
    max(case when (upper(t.AncillaryRole)=upper(d.Code) and upper(d.Field)=upper('AncillaryRole'))
    then Description end) as AncillaryRole_DESC
    from CGIdb.CGI1.[LookUpTbl2] d
    where
    (upper(t.AOD_METHOD)=upper(d.Code) and upper(d.Field)=upper('AOD_METHOD')) or
    (upper(t.OWNER)=upper(d.Code) and upper(d.Field)=upper('OWNER')) or
    (upper(t.ID_DMA_CODE)=upper(d.Code)and upper(d.Field)=upper('ID_DMA_CODE')) or
    (upper(t.ID_CONTROL_GROUP)=upper(d.Code) and upper(d.Field)=upper('ID_CONTROL_GROUP')) or
    (upper(t.OPERATIONAL_STATUS)=upper(d.Code) and upper(d.Field)=upper('OPERATIONAL_STATUS')) or
    (upper(t.SOURCE_METHOD)=upper(d.Code)and upper(d.Field)=upper('SOURCE_METHOD')) or
    (upper(t.QA_STATUS)=upper(d.Code) and upper(d.Field)=upper('QA_STATUS')) or
    (upper(t.SECURITY_IND)=upper(d.Code)and upper(d.Field)=upper('SECURITY_IND')) or
    (upper(t.Enabled)=upper(d.Code) and upper(d.Field)=upper('Enabled')) or
    (upper(t.AncillaryRole)=upper(d.Code) and upper(d.Field)=upper('AncillaryRole'))
    ) as d

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Just curious - but how are these solutions better than using multiple joins?  If this is a performance related issue - fix the use of the upper functions on the columns, especially since those can easily be managed in a case-sensitive collation by ensuring the data in the tables have the appropriate case applied - and if the collation is case-insensitive then they are not needed anyways.

    Why make the code more complex than is needed - when the left outer joins solve the problem?

    Yes...it can be done, in fact - you could probably create an inline-table valued function to perform the lookup and simplify the code, but that would still be more complex than a simple outer join.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Just curious - but how are these solutions better than using multiple joins?  If this is a performance related issue - fix the use of the upper functions on the columns, especially since those can easily be managed in a case-sensitive collation by ensuring the data in the tables have the appropriate case applied - and if the collation is case-insensitive then they are not needed anyways.

    Why make the code more complex than is needed - when the left outer joins solve the problem?

    Yes...it can be done, in fact - you could probably create an inline-table valued function to perform the lookup and simplify the code, but that would still be more complex than a simple outer join.

    Each join requires a full scan of the table.  The single outer apply only requires one scan.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Except that single outer apply will be executed for each row in the Meter table - and the data must be sorted each time based on the filters from the Meter table.

    SQL Server may be able to scan the table a single time - but it will still need to read every row in the table for each row in Meter to find the matching rows with a sort that isn't necessary.

    I still think the problem is performance related (but the OP hasn't stated) - and to fix that they need to remove the UPPER function calls.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Except that single outer apply will be executed for each row in the Meter table - and the data must be sorted each time based on the filters from the Meter table.

    SQL Server may be able to scan the table a single time - but it will still need to read every row in the table for each row in Meter to find the matching rows with a sort that isn't necessary.

    I still think the problem is performance related (but the OP hasn't stated) - and to fix that they need to remove the UPPER function calls.

    Not necessarily, presumably SQL will use a lazy spool if warranted.

    What sort?  SQL will have to use an aggregate, but I don't see the need for a sort.

    In real life I've had the code above perform much better overall than all the joins, but of course that's not guaranteed, you'd have to verify for a specific case.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I have written on numerous occasions that Lookup Tables is one of the more complex and interesting features in Autodesk Revit MEP. At the same time, there is almost no information on it on the net. I even tried to order an essay here aplusessay.com/pay-for-assignment.html on this topic to understand how it works.

    So that's it. The size_lookup function provides the ability to look up numeric values only. The size_lookup function is for instance parameters. If you need to use data to define the types of an object with unique values for each type. Maybe you can still extract text from the lookup table? Any ideas?

    • This reply was modified 1 month, 1 week ago by  vondes.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply