Help using LIKE and IN(select product from referenceTable) and wildcards

  • Here's the issue/problem:

    I've got a reference table with a list of software 'products'

    I've got a product table with a list of systems and the software 'products' installed.

    I want to retrieve records from the product table where the product in LIKE something in the reference table.

    Here is some sample code:

    /* Create Reference Table */

    declare @softwareReferenceList table(pk_CodeID int, Product nvarchar(50))

    insert into @softwareReferenceList

    select 1, 'Excel%' union

    select 2, 'CWS%' union

    select 3, 'PowerPoint%' union

    select 4, 'SQL%' union

    select 5, 'ZENworks%'

    /*select * from @softwareReferenceList order by 2*/

    /* Create SAMPLE Product Table */

    declare @productList table(productID int, Product nvarchar(50))

    insert into @productList

    select 1622, 'Excel 2003' union

    select 1559, 'Excel 2007' union

    select 1100, 'Excel 2010' union

    select 1140, 'Google Chrome' union

    select 3605, 'CWS_CMS' union

    select 1234, 'Oracle Database 10g Client' union

    select 1235, 'Oracle Database 11g Client' union

    select 1236, 'Oracle Database 11g Instant Client' union

    select 1237, 'Oracle Drive' union

    select 1238, 'Oracle8 Client' union

    select 3602, 'PowerPoint 2003' union

    select 3532, 'PowerPoint 2007' union

    select 1800, 'PowerPoint 2010' union

    select 1120, 'SQL Enterprise Manager' union

    select 103, 'SQL Server 2005' union

    select 96, 'SQL Server 2008 Express Edition' union

    select 1155, 'SQL Server Management Studio' union

    select 35, 'ZENworks Bundle Module' union

    select 126, 'ZENworks Imaging Agent' union

    select 1070, 'ZENworks Imaging Module' union

    select 33, 'ZENworks Inventory Module'

    /* Return ALL records from @productList that are LIKE products in @softwareReferenceList */

    select *

    from @productList

    /*where Product like in(select Product from @softwareReferenceList)*/

    order by 2

    The results should look like this:

    productIDProduct

    3605CWS_CMS

    1622Excel 2003

    1559Excel 2007

    1100Excel 2010

    3602PowerPoint 2003

    3532PowerPoint 2007

    1800PowerPoint 2010

    1120SQL Enterprise Manager

    103SQL Server 2005

    96SQL Server 2008 Express Edition

    1155SQL Server Management Studio

    35ZENworks Bundle Module

    126ZENworks Imaging Agent

    1070ZENworks Imaging Module

    33ZENworks Inventory Module

    Now, I could build a cursor and using RBAR get the data, but I'd prefer not to have to do that.

    I have searched this forum for an answer, but didn't find one.

    Any and all help would be appreciated.

    Thanks


    Living in Paradise: N 34°16'07.99" W 119°12'17.58"

  • Take a look at this.

    A simple JOIN or EXISTS can do the trick.

    /* Return ALL records from @productList that are LIKE products in @softwareReferenceList */

    select p.*

    from @productList p

    JOIN @softwareReferenceList s ON p.Product LIKE s.Product

    /*where Product like in(select Product from @softwareReferenceList)*/

    order by 2

    select p.*

    from @productList p

    WHERE EXISTS( SELECT 1 FROM @softwareReferenceList s WHERE p.Product LIKE s.Product)

    /*where Product like in(select Product from @softwareReferenceList)*/

    order by 2

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Perfect

    Thanks


    Living in Paradise: N 34°16'07.99" W 119°12'17.58"

Viewing 3 posts - 1 through 2 (of 2 total)

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