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 */
/*where Product like in(select Product from @softwareReferenceList)*/
order by 2
The results should look like this:
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.
Living in Paradise: N 34°16'07.99" W 119°12'17.58"