Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
tim.hansen
tim.hansen
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 343
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"
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8564 Visits: 18143
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
tim.hansen
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 343
Perfect
Thanks


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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search