March 22, 2010 at 5:35 pm
I have 3 tables:
computer
computer_lists
software
Table 'computer' contains network computers.
Table 'computer_lists' contains software installed on network computers returned by audit (list type = Software)
Table Software contains approved Software list.
I would like to identify all Software that appears in 'computer_lists' that is not in table 'software' (i.e. is not approved).
Quite a few issues. There is no link between the tables of interest ('computer_lists' , 'software').
'product_name in table software will be LIKE 'value' in table 'computer_lists'.
Table 'computer_lists' also contains Security Updates, Hotfixes etc (really not interested in any of those).
I have attached sample code.
The code will create the 3 tables and insert test data (10000 rows for 'computer_lists').
Can anyone see a solution taking into account there is no relationship?
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 23, 2010 at 7:58 am
I'm not sure you have the option, but it would be easier if hotfixes, etc... went under another list_type or list_types than "Software" or put all the hotfixes and Windows Updates in the Software list.
Without doing that your query would need to be something like this:
SELECT
C.computer_id,
C.computer_name,
CL.value
FROM
dbo.computer AS C JOIN
dbo.computer_lists AS CL ON
C.computer_id = CL.computer_id LEFT JOIN
dbo.software AS S ON
CL.VALUE LIKE '%' + S.product_name + '%' OR
S.product_name LIKE '%' + CL.VALUE + '%'
WHERE
CL.list_type = 'software' AND
(
CL.VALUE NOT LIKE '%Update%' AND
CL.VALUE NOT LIKE '%Hotfix%' AND
CL.VALUE NOT LIKE '%KB[0-9]%'
) AND
S.software_id IS NULL
You'd obvioiusly have to add more to your list of CL.Value NOT LIKE. I tried to provide some generic options, but I didn't hit them all.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 23, 2010 at 9:27 am
Thanks Jack. Third party app so I have no control!
Once again, thanks for your time.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 23, 2010 at 6:45 pm
Phil,
This is my reply to your DM, just in case someone else has the same questions.
Any operator you can put in the WHERE clause can go in a JOIN. You can use LIKE, =, <>, etc... You can also have CASE Statements and functions. Not that I recommend anything but = in most cases. Your case requires the LIKE operator. It is not ideal because it means that the best you can get is an index scan, but you have to do it. The OR is not ideal either, but it is necessary, in my opinion.
Brackets in the LIKE operation are really cool. It is similar to regular expressions as the brackets allow you to put a group or range. In this case it means the letters KB followed by any number then the % means any number of any characters after that. So KB0.... through KB9.... match the pattern.
Your requirement is to find any installed software in the computer_list table where the software does NOT exist in the software table. This requires a LEFT JOIN, give me all the rows in computer_list, which means any non-nullable column in the RIGHT table (software) will return a NULL when the JOIN columns do not match, thus the IS NULL eliminates any rows where you DO have a match on the JOIN. Gail Shaw (GilaMonster here on SSC) has an excellent blog post today about these kind of situations that actually shows another method that can be better performing.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 23, 2010 at 7:20 pm
Another approach would be the use of a NOT EXISTS test in your WHERE clause, something like this
SELECT ...
FROM computer_lists
WHERE NOT EXISTS (select 1 from software where software.product_name = computer_lists.value)
or
WHERE NOT EXISTS (select 1 from software where software.product_name LIKE '%'+computer_lists.value+'%')
By the way, why do you feel there is no relationship here? Just because there isn't a foreign key on the product_name or value columns doesn't mean there isn't a usable relationship.
Edited to add: Sorry, Jack. I just noticed your link to Gail's blog, which discusses NOT EXISTS.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 23, 2010 at 7:31 pm
The Dixie Flatline (3/23/2010)
Another approach would be the use of a NOT EXISTS test in your WHERE clause, something like thisSELECT ...
FROM computer_lists
WHERE NOT EXISTS (select 1 from software where software.product_name = computer_lists.value)
or
WHERE NOT EXISTS (select 1 from software where software.product_name LIKE '%'+computer_lists.value+'%')
By the way, why do you feel there is no relationship here? Just because there isn't a foreign key on the product_name or value columns doesn't mean there isn't a usable relationship.
Edited to add: Sorry, Jack. I just noticed your link to Gail's blog, which discusses NOT EXISTS.
No problem. nothing wrong with putting the alternative here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 24, 2010 at 4:00 am
Thanks. I did not feel there was a relationship because there was no FK.
I will take a look at the blog also.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 24, 2010 at 5:34 am
You could also try something like
Select *
From Computer_lists
where list_type = 'software'
and value not in (Select Distinct Product_name from Software)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply