Search through multiple columns and find first occurence of value from a list

I am in the process of doing some field mapping, and the first step is to clean up some existing data.  Here is what I need to accomplish

table 1  -
UniqueID Category1 Category2 Category3 Category4
 123             100          200          300             400

CategoryID CategoryDescription
   300               doohickeys

For each uniqueID in table1, I need to search through each category column, and find the first categoryID that is in table2.  
Based on the example above, I would expect that ID 123 would return 300.

I was thinking of creating a udf with each category column as a parameter.  It would evaluate the 1st column..does it exist in table2.  Yes - return category and exit function.  No - move on to the next column.  If nothing was found return a deafault value of -1.  

I wanted to see if there was an easier, or more efficient way of doing this.  

Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
To avoid multiple joins/subqueries on the lookup table:

SELECT UniqueID, (
    SELECT TOP 1 table2.CategoryID
    FROM table2
    WHERE table2.CategoryID IN (table1.Category1, table1.Category2, table1.Category3, table1.Category4)
    ORDER BY CASE table2.CategoryID
        WHEN table1.Category1 THEN 1
        WHEN table1.Category2 THEN 2
        WHEN table1.Category3 THEN 3
        WHEN table1.Category4 THEN 4 END
    ) AS FirstCategoryFound                  
FROM table1
Patrick MatthewsCommented:
    (SELECT t2.CategoryID FROM Table2 t2 WHERE t1.Category1 = t2.CategoryID),
    (SELECT t2.CategoryID FROM Table2 t2 WHERE t1.Category2 = t2.CategoryID),
    (SELECT t2.CategoryID FROM Table2 t2 WHERE t1.Category3 = t2.CategoryID),
    (SELECT t2.CategoryID FROM Table2 t2 WHERE t1.Category4 = t2.CategoryID),
    -1) AS Category
FROM Table1 t1
Assuming that there are not duplicate descriptions for categoryID's in table2, you could do this:

SELECT UniqueID, isnull(t2a.CategoryDescription, t1.Category1), isnull(t2b.CategoryDescription, t1.Category2),
      isnull(t2c.CategoryDescription, t1.Category3), isnull(t2d.CategoryDescription, t1.Category4)
FROM table1 t1
   LEFT JOIN table2 t2a ON t1.Category1 = t2a.CategoryID
   LEFT JOIn table2 t2b ON t1.Category2 = t2b.CategoryID
   LEFT JOIN table2 t2c ON t1.Category3 = t2c.CategoryID
   LEFT JOIN table2 t2d ON t1.Category4 = t2d.CategoryID
rmartin15Author Commented:
thanks a lot.  ran quickly, and gave what i need
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.