SQL Query Where Results not exist in a different table 1

[JEngles]

I know this should be a failt simple question, but I've been searching for days and I can't find the solution...and yes I'm a newbie to SQL.

This is what I have so far:

SELECT DISTINCT Publisher 'PUBLISHER', Name 'NAME'
FROM Inv_AeX_OS_Add_Remove_Programs
WHERE NOT EXISTS (
SELECT * FROM Assyst_Gateway_Filter
WHERE Inv_AeX_OS_Add_Remove_Programs.Name LIKE Assyst_Gateway_Filter.AppName
)

I'd much appreciate some help.

Cheers,
John

 

[markros]

Can you explain your problem (preferably with input and desired output statements)? Your current query is OK except I would use = instead of LIKE.

Check also question #4 in this blog post

http://blogs.lessthandot.com/index..../the-ten-most-asked-sql-server-questions--1#4

PluralSight Learning Library

 

[JEngles]

So Name in Inv_AeX_OS_Add_Remove_Programs contains data like this:

ActivePerl Build 622
ActualTests Exam Engine - VMWare VCP-410
Administration Tools for Print Management
Adobe Acrobat 7.0 Elements
APSoil
ArcGIS (MWC License)
ATI Problem Report Wizard
Catalyst Control Center Graphics Full Existing
Security Update for Windows XP (KB982802)
Service Pack 1 for SQL Server 2008 (KB968369)
Update for Windows XP (KB980182)
User Profile Hive Cleanup Service
Windows 2000 Hotfix - KB883935

and AppName in Assyst_Gateway_Filter contains:

(KB
ATI
Graphic
Profile Hive Cleanup
Update for
Windows 2000 Hotfix
Windows XP Hotfix
Windows Driver Package

My results would come out like this:

ActivePerl Build 622
ActualTests Exam Engine - VMWare VCP-410
Administration Tools for Print Management
Adobe Acrobat 7.0 Elements
APSoil
ArcGIS (MWC License)

Unfortunately to complicate the issue, the data in Assyst_Gateway_Filter will be added to, so the idea of a more dynamic solution where we won’t need to edit the query manually for every new filter word is needed.

 

[markros]

Try

SELECT DISTINCT Publisher 'PUBLISHER', Name 'NAME'
FROM Inv_AeX_OS_Add_Remove_Programs P
WHERE NOT EXISTS (
	SELECT * FROM Assyst_Gateway_Filter F
	WHERE P.Name LIKE '%' + F.AppName + '%')

This should work for the data you showed.

PluralSight Learning Library

 

[JEngles]

Markros, you're a bloody legend.

That worked perfectly!!