Complex: JOIN with LIKE, but only for NULL results

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex: JOIN with LIKE, but only for NULL results

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2014-05-29 : 11:44:39

The subject line doesn't quite cover it well enough to fully describe the scenario, so I'll try to lay this out as best I can:

I have two tables of data, and need to determine which PCs have not received any updates.

Table with PCs: tb_assets
Relevant Field Name: f_assettag

Table with Updates: tb_auditlog
Relevant Field Name: f_auditdescription

I need to get a list of the asset tags where there is no audit description (f_auditdescription) with an update note. Each time an update is installed, it has an entry in tb_auditlog under f_auditdescription like "The update 'Update for Windows 7 (KB2913152)' has been installed on 000123456".

The sample query (I also tried CTEs with no luck) that may better describe what I'm trying to do is below:


SELECT f_assettag, f_auditdescription
FROM tb_auditlog
RIGHT JOIN tb_assets
ON f_auditdescription LIKE '%' + f_assettag + '%'
WHERE f_auditdescription IS NULL

The further part of the problem is that the Audit Log contains many other items besides the update installations. So to limit the query, I need to specifically query (in plain English):
Give me all of the machines that have no audit description like "has been installed on"

Thanks in advance for any help you can offer,
Matt

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-29 : 12:01:27

How can the 'ON' condition and the 'WHERE' condition both be true at the same time?

Post some sample data and we'll take a look at it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-29 : 12:46:05

There are lots of ways to write this, but if you break it apart logically you can combine it into something like this:

SELECT 
	*
FROM 
	tb_assets
LEFT OUTER JOIN
	(
		SELECT DISTINCT f_assettag 
		FROM tb_auditlog
		WHERE f_auditdescription LIKE '%has been installed on%'
	) AS T
	ON tb_assets.f_assettag = T.f_assettag
WHERE
	tb_assets.f_assettag IS NULL

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2014-05-29 : 16:41:09

I'll whip up some sample data and post it back here (likely tomorrow morning)

Lamprey, I cannot replicate any results from your query, likely because "f_assettag" is not a field in the "tb_auditlog" table. If I choose any other random field from that table, I still receive zero rows.

Thanks,
Matt

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-29 : 17:24:38

I didn't have data to run that against, but my intention was to check the serviced table "T" for null in the predicate, not the way I posted it. So, sample data would be the way to go to help illustrate the issue. Plus, we can run queries against it and provide tested code.

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2014-09-02 : 12:42:31

I started over from scratch. Rather than storing the "Update installed" data solely inside the "tb_auditlog" table, there is a second table merely for recording updates that have been installed.

Now, to query for machines that have no updates installed, I just run a simple join and a small CTE:


WITH cte_updatesmissing AS (
	SELECT f_assettag, COUNT(DISTINCT f_updateinstalledID) AS f_totalupdatesinstalled 
	FROM tb_assets
	LEFT JOIN tb_updatesinstalled ON f_updateinstalledmachine = f_assettag
	GROUP BY f_assettag
	)
SELECT f_assettag, f_updateinstalledID
FROM cte_updatesmissing
WHERE f_totalupdatesinstalled = 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources