Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help using LIKE and IN(select product from referenceTable) and wildcards Expand / Collapse
Author
Message
Posted Tuesday, September 3, 2013 5:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:05 PM
Points: 178, Visits: 337
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:
productID	Product
3605 CWS_CMS
1622 Excel 2003
1559 Excel 2007
1100 Excel 2010
3602 PowerPoint 2003
3532 PowerPoint 2007
1800 PowerPoint 2010
1120 SQL Enterprise Manager
103 SQL Server 2005
96 SQL Server 2008 Express Edition
1155 SQL Server Management Studio
35 ZENworks Bundle Module
126 ZENworks Imaging Agent
1070 ZENworks Imaging Module
33 ZENworks 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"
Post #1491117
Posted Tuesday, September 3, 2013 6:40 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:03 PM
Points: 3,486, Visits: 7,531
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1491120
Posted Wednesday, September 4, 2013 8:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:05 PM
Points: 178, Visits: 337
Perfect
Thanks



Living in Paradise: N 34°16'07.99" W 119°12'17.58"
Post #1491347
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse