[Last Call] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2007-10-15
Medium Priority
Last Modified: 2008-01-09
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.  

Question by:rmartin15
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20080605
    (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
LVL 11

Expert Comment

ID: 20080617
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
LVL 70

Accepted Solution

Scott Pletcher earned 1000 total points
ID: 20081381
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

Author Comment

ID: 20090137
thanks a lot.  ran quickly, and gave what i need

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question