Help with joining to a table with "wildcard" data

• LeeFAR

  SSCarpal Tunnel

  Points: 4644

  More actions

  May 11, 2017 at 3:08 pm

  #321317

  I apologize up front as I'm not going to describe very well what I'm attempting to do.  I hope that if you look at the attached script, you will get a better sense. 

  But I have a code lookup table that I must join transactional data to.  You join to the code lookup table on three fields.  If you join on all three conditions, then you get a record.  However, if you don't match all three fields and you match on two, then you have to pick up the record with a wildcard.  And if you join on only one, then you have to pick up the record with a wildcard in the other two fields.  Again, I realize this is confusing and problem why I can't get this right.  Hopefully the script and sample data will explain this better.  Please let me know if there is anything more I can provide.

  The results I'm getting are this.
  

  The results I need are this.
  

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  May 11, 2017 at 3:17 pm

  #1942240

  Try this:

  
SELECT T.InvoiceNumber, T.EntryCode AS InvoiceEntryCode, T.StoreNumber AS InvoiceStoreNumber, T.RptLevel AS InvoiceRptType,
OA1.*
FROM #tmpTransactions T
   OUTER APPLY (
    SELECT TOP (1) *
    FROM #tmpCodeLookup EM
    WHERE (T.EntryCode = EM.EntryCode) AND
            ((T.StoreNumber = EM.StoreNumber AND T.RptLevel = EM.RptLevel) OR
             (T.StoreNumber = EM.StoreNumber AND '*' = EM.RptLevel) OR
             ('*' = EM.StoreNumber AND '*' = EM.RptLevel))
    ORDER BY CASE WHEN T.StoreNumber = EM.StoreNumber THEN 0 ELSE 1 END,
              CASE WHEN T.RptLevel = EM.RptLevel THEN 0 ELSE 1 END
   ) AS OA1

  Edit: Aligned SQL code better.
  Edit2: Btw, be sure to cluster the CodeLookup table on ( EntryCode, StoreNumber, RptLevel ) rather than on, say, identity!

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• LeeFAR

  SSCarpal Tunnel

  Points: 4644

  More actions

  May 11, 2017 at 3:32 pm

  #1942241

  ScottPletcher - Thursday, May 11, 2017 3:17 PM

  Try this:

  
SELECT T.InvoiceNumber, T.EntryCode AS InvoiceEntryCode, T.StoreNumber AS InvoiceStoreNumber, T.RptLevel AS InvoiceRptType,
OA1.*
FROM #tmpTransactions T
   OUTER APPLY (
    SELECT TOP (1) *
    FROM #tmpCodeLookup EM
    WHERE (T.EntryCode = EM.EntryCode) AND
            ((T.StoreNumber = EM.StoreNumber AND T.RptLevel = EM.RptLevel) OR
             (T.StoreNumber = EM.StoreNumber AND '*' = EM.RptLevel) OR
             ('*' = EM.StoreNumber AND '*' = EM.RptLevel))
    ORDER BY CASE WHEN T.StoreNumber = EM.StoreNumber THEN 0 ELSE 1 END,
              CASE WHEN T.RptLevel = EM.RptLevel THEN 0 ELSE 1 END
   ) AS OA1

  Edit: Aligned SQL code better.
  Edit2: Btw, be sure to cluster the CodeLookup table on ( EntryCode, StoreNumber, RptLevel ) rather than on, say, identity!

  Scott, you rock.  Thank you so much for this.  I'm studying it to make sure I get what you're doing.  But thanks again for the quick response.