Query issue... Selecting values that include any of the words from a list

[molona]

Hi SQL experts! Need some help here

I have a database that lists the software installed in each computer in my company, and on the other side I have a list of software and the number of the version compatible with win 7

I have to cross the data between these two tables. I need to know which software in the first list includes the software listed in the second list.

An example.

The first list shows the software installed in various computes, and it will look like

7-Zip 4.65	Igor Pavlov	4.65.00.0
Adobe Acrobat 9 Standard - Italiano, Español, Nederlands, Português	Adobe Systems	9.0.0
Adobe Flash Player 10 ActiveX	Adobe Systems, Inc.	10.0.12.36
Adobe Reader 9 - Español	Adobe Systems Incorporated	9.0.0
Adobe SVG Viewer 3.0	Adobe Systems, Inc.	3.0
Altiris Application Metering Agent	Altiris Inc.	7.0.1255.0
Altiris Inventory Agent	Symantec	7.0.1218.0
Broadcom Management Programs	Broadcom	7.74.01
Broadcom NetXtreme Ethernet Controller	Broadcom	7.51.01
CA Unicenter Software Delivery	Computer Associates
Configuration Manager Client	Microsoft Corporation	4.00.6487.2000
DWG TrueView	Autodesk	16.2.54.20

But on the second list, the sofware looks like

ACROBAT		9
DISTILLER
CIVILFEM		13
AUTOCAD	2010
AUTOSKETCH		10
DAMEWARE		6.9
DWG TrueView	2010
EXCEED		14
EXCEED OnDEMAND		13
FILEMAKER		10

As you can see, the names of the software in both list don't match. Therefore, I need to do a query that gives as a result the software that includes any of the words from the second list.

My goal is to compare the versions of each software to know if what he has installed will be compatible in his new computer.

Thanks

[guido2004]

Something like this?

SELECT
  ...
FROM table1
INNER JOIN table2 
ON UPPER(table1.name) LIKE CONCAT('%',UPPER(table2.name),'%')

[molona]

That doesn't work. If I use the SQL view in Access, it says that the functions UPPER and CONCAT are not defined. If I remove the functiions and use a concatenation operator instead, it gives an error saying that the JOIN expresion is not admisible

[guido2004]

Ahhh, Access... :S
Let's see if I can rewrite that query.

[guido2004]

Try this

SELECT
  ...
FROM table1
INNER JOIN table2 
ON (UCase(table1.name) LIKE '%' & UCase(table2.name) & '%'))

I didn't test it. You might have to add some brackets here and there to satisfy one of Bill's strange creatures.

[molona]

That actually did something... but brought no results after a long time thinking (there are like 768,000 files in the original table although Access 2007 seems to handle well enough, in this case it has been thinking for a little while)

[guido2004]

That actually did something... but brought no results after a long time thinking (there are like 768,000 files in the original table although Access 2007 seems to handle well enough, in this case it has been thinking for a little while)

Maybe you have to trim the value in the LIKE (it will slow the query down even further):

SELECT
  ...
FROM table1
INNER JOIN table2 
ON (UCase(table1.name) LIKE '%' & UCase(Trim(table2.name)) & '%')

[molona]

Nothing. Empty result

[guido2004]

Stupid Access. Wildcard isn't %, but *

SELECT
  ...
FROM table1
INNER JOIN table2 
ON (UCase(table1.name) LIKE '*' & UCase(Trim(table2.name)) & '*')

[r937]

That doesn't work. If I use the SQL view in Access...

just another example pointing out how important it is for the original poster to mention which database system he or she is using

[molona]

just another example pointing out how important it is for the original poster to mention which database system he or she is using

true. That was my bad. Apologies. I'm currently using Access because this is just a punctual job. The information may be updated in the future but right now I have to get the list of compatible software by comparing the version number.

1600 computers are being migrated to Windows 7 and 8. This list only include 1000 of them and I have a second list with the name of each software and the version that we know it is compatible.

Yet, the names in one table and the other doesn't match. The first table, the one that tells me what's really installed in the user's computer, is an export of the information gathered by a program and it reads what's in the Add/Remove programs lists in WinXP.

So I have things like "Microsoft Office 2007 Professional", "Microsoft Office 2003 Standard", "2007 Microsoft Office web components" and things like that.

Of course, it is not only a case of selecting what's related to Microsoft Office. The list has 80 software product names that need to be compared

[guido2004]

true. That was my bad. Apologies. I'm currently using Access because this is just a punctual job. The information may be updated in the future but right now I have to get the list of compatible software by comparing the version number.

1600 computers are being migrated to Windows 7 and 8. This list only include 1000 of them and I have a second list with the name of each software and the version that we know it is compatible.

Yet, the names in one table and the other doesn't match. The first table, the one that tells me what's really installed in the user's computer, is an export of the information gathered by a program and it reads what's in the Add/Remove programs lists in WinXP.

So I have things like "Microsoft Office 2007 Professional", "Microsoft Office 2003 Standard", "2007 Microsoft Office web components" and things like that.

Of course, it is not only a case of selecting what's related to Microsoft Office. The list has 80 software product names that need to be compared

So did you try my latest version of the query? Post #9.

[molona]

@guido2004 ; No changes so far

What I've been doing is to add a new field to write an alias (say that everything that's Microsoft Office related but not an update or a component or similar, will have a label "Microsof Office" in this new field. Same with each software.

But it is so much work and there has to be a better way. Althogh this crappy way will save me for tomorrow

[guido2004]

@guido2004 ; No changes so far

What I've been doing is to add a new field to write an alias (say that everything that's Microsoft Office related but not an update or a component or similar, will have a label "Microsof Office" in this new field. Same with each software.

But it is so much work and there has to be a better way. Althogh this crappy way will save me for tomorrow

I created two tables in Access:

table1:
Application text(255)
Company text(255)
Version text(50)

table2:
Application text(255)
Version text(50)

Then I imported the data you posted in the first post.

And then I ran the query from my post #9:

SELECT
  *
FROM table1
INNER JOIN table2 
ON (UCase(table1.name) LIKE '*' & UCase(Trim(table2.name)) & '*')

I get two rows as a result (as should be):

table1.Application                                                      Company           table1.Version    table2.Application	table2.Version
Adobe Acrobat 9 Standard - Italiano, Español, Nederlands, Português     Adobe Systems     9.0.0             ACROBAT             9
DWG TrueView                                                            Autodesk          16.2.54.20        DWG TrueView        2010

[molona]

Did I say that I didn't work? Well, I was wrong. The results are not as expected though. It doesn't always gets it right and associates the right program. I didn't remove one of the % (although Access is supposed to work with % too) It did go quite quickly this time (surprisingly since the others run slooooow).

[guido2004]

The results are not as expected though. It doesn't always gets it right and associates the right program.

For example?

[molona]

Following the Acrobat example, I get these results

      

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ADOBE ACROBAT

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ESPA

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ADOBE ACROBAT

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ESPA

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ADOBE ACROBAT

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ESPA

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ESPA

Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português
ADOBE ACROBAT

The reason is that in my list of software to check, there is one used in this company (I think they created themseselves) called ESPA. I can understand that since "Español" is in the description of Adobe Acrobat, it can get confused. I don't understand why sometimes it is matched as "ADOBE ACROBAT" and sometimes as "ESPA".

Of course that happens with other software which add the language on their description too.

Exceed is another exmple.

They use Hummingbird Exceed and Excee OnDemand. The query result sometimes matches Exceed OnDemand correctly, and sometimes it matches the entry with "Exceed".

      

Hummingbird Exceed onDemand Client
EXCEED

Hummingbird Exceed onDemand Client
EXCEED

Hummingbird Exceed onDemand Client
EXCEED OnDEMAND

Hummingbird Exceed onDemand V3.2
EXCEED OnDEMAND

Hummingbird Exceed onDemand V3.2
EXCEED

Hummingbird Exceed onDemand V3.2
EXCEED

[guido2004]

Those aren't errors. If you use the LIKE function, it will return any row with that string in the column you're checking.

"Hummingbird Exceed onDemand Client" contains the string "EXCEED", so it will be returned for that.
"Hummingbird Exceed onDemand Client" contains the string "EXCEED OnDEMAND" as well, so it will be returned for that too.

You will get each such row from table1 twice.

The same for your other example.

That's the kind of problems you get when you work with completely non normalised databases. I have no idea how you could resolve that, if not by going through the results by hand.

[molona]

Those aren't errors. If you use the LIKE function, it will return any row with that string in the column you're checking.

I know that these aren't errors "per se" but they still need to be solved. And you're right, this is what happens in this type of situation.

It would be interesting to know why he chooses one or the other option, though. Then maybe I could figure out how to solve it.

Thanks so much for your help.

[guido2004]

It would be interesting to know why he chooses one or the other option, though. Then maybe I could figure out how to solve it.

Nothing is chosen. All hits are returned. In the examples you posted, each row from table1 that contains "Hummingbird Exceed onDemand Client" will be returned twice. The same for each row from table1 with "Adobe Acrobat 9 Standard - Italiano, Español, Nederlands, Português".

[r937]

sounds like a job for DISTINCT ...

[molona]

You will get each such row from table1 twice.

I am not sure about this because the number don't match. But that may be because some records don't have a language description on them and therefore "ESPA" will not match at all.

[molona]

sounds like a job for DISTINCT ...

I will try with the user names inlcuded. If I use DISTINCT with only the software included, 1463 registries that include acrobat get reduced to 70 but I still have some versions matched with "ADOBE ACROBAT" and some others with "ESPA"