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

Databases
1

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

 [TABLE]

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

But on the second list, the sofware looks like
[TABLE]
Lista_Software [TR]
Nombre_Software version_nombre_comercial version_Compatible_w7 [/TR]
[TR]
[TD]ACROBAT[/TD]
[TD]
[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]DISTILLER[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]CIVILFEM[/TD]
[TD]
[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]AUTOCAD[/TD]
[TD]2010[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]AUTOSKETCH[/TD]
[TD]
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]DAMEWARE[/TD]
[TD]
[/TD]
[TD]6.9[/TD]
[/TR]
[TR]
[TD]DWG TrueView[/TD]
[TD]2010[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]EXCEED[/TD]
[TD]
[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]EXCEED OnDEMAND[/TD]
[TD]
[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]FILEMAKER[/TD]
[TD]
[/TD]
[TD]10[/TD]
[/TR]
[/TABLE]

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

2

Something like this?


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

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 :frowning:

4

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

5

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.

6

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

7

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)) & '%')
8

Nothing. Empty result :frowning:

9

Stupid Access. Wildcard isn’t %, but *


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

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

11

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 :slight_smile:

12

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

13

@guido2004; No changes so far :frowning:

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 :smiley:

14

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
15

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).

16

For example?

17

Following the Acrobat example, I get these results

      [TABLE]
[TR]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ADOBE ACROBAT[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ESPA[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ADOBE ACROBAT[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ESPA[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ADOBE ACROBAT[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ESPA[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ESPA[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Adobe Acrobat  9 Standard - Italiano, Español, Nederlands, Português[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]ADOBE ACROBAT[/COLOR][/FONT][/TD]
[/TR]
[/TABLE]

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”.

      [TABLE]
[TR]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Hummingbird Exceed onDemand Client[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]EXCEED[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Hummingbird Exceed onDemand Client[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]EXCEED[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Hummingbird Exceed onDemand Client[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]EXCEED OnDEMAND[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Hummingbird Exceed onDemand V3.2[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]EXCEED OnDEMAND[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Hummingbird Exceed onDemand V3.2[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]EXCEED[/COLOR][/FONT][/TD]
[/TR]
[TR]
[TD][FONT=Calibri][COLOR=#000000]Hummingbird Exceed onDemand V3.2[/COLOR][/FONT][/TD]
[TD][FONT=Calibri][COLOR=#000000]EXCEED[/COLOR][/FONT][/TD]
[/TR]
[/TABLE]
18

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.

19

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.

20

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”.