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

• tim.hansen

  SSC Veteran

  Points: 296

  More actions

  September 3, 2013 at 5:10 pm

  #281961

  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"

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 3, 2013 at 6:40 pm

  #1647167

  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

• tim.hansen

  SSC Veteran

  Points: 296

  More actions

  September 4, 2013 at 8:48 am

  #1647346

  Perfect

  Thanks

  ---

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