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

Posted on 2007-10-15
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 92

    Expert Comment

    by:Patrick Matthews
        (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

    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 68

    Accepted Solution

    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
    --ORDER BY UniqueID

    Author Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how the fundamental information of how to create a table.

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now