CODEWITHSUNDEEP
sql-serverselectconditional-statements
DJMcCarthy12
DJMcCarthy12

Reputation: 4119

MSSQL Select rows not matching criteria

Wasn't really sure how to word the question - heres the situation and what I am trying to do. I have a table like this:

ProductName    Version

Excel          1.0.1
Word           1.0.2
Adobe          2.0.4

The purpose of this table is just to show me the most recent version of the related product. What I want to do is search through another table I have with a structure as such:

Hostname    ProductName    ProductVersion    .... Many other columns

Sample1     Excel          1.0.0
Sample2     Excel          1.0.1
Sample2     Adobe          2.0.4
etc...

I want to take the product names/versions from the first table, and look through the second table for any host that is running anything but the latest version of the software. The productnames will differ a bit between the two tables, but ProductName like '%excel%' should work fine enough.

I really don't know where to start here - any help would be appreciated!

Upvotes: 0

Views: 86

Answers (2)

adrianm
adrianm

Reputation: 14736

Do it the other way around. For each row in the second table, check if the same product and version exists in the first table.

SELECT * 
FROM table2
WHERE NOT EXISTS (SELECT 1
                  FROM table1
                  WHERE table1.ProductName LIKE '%' + table2.ProductName + '%'
                        AND table1.version <> table2.ProductVersion)

Upvotes: 0

Chris Curvey
Chris Curvey

Reputation: 10409

select table2.*
from table2 
, table1
where charindex(table1.ProductName, table2.ProductName) > 0
and table2.version != table1.version

The charindex match for version strings is a hack that may not work. It depends on the ProductName in Table2 being a substring of the values in Table1. YMMV.

And as your tables get bigger, this will slow down dramatically.

Upvotes: 1

Related Questions