SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)

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

    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

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Something like this?
    Code:
    SELECT
      ...
    FROM table1
    INNER JOIN table2 
    ON UPPER(table1.name) LIKE CONCAT('%',UPPER(table2.name),'%')

  3. #3
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    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

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Ahhh, Access... :S
    Let's see if I can rewrite that query.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Try this
    Code:
    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. #6
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    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)

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    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):
    Code:
    SELECT
      ...
    FROM table1
    INNER JOIN table2 
    ON (UCase(table1.name) LIKE '%' & UCase(Trim(table2.name)) & '%')

  8. #8
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    Nothing. Empty result

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Stupid Access. Wildcard isn't %, but *
    Code:
    SELECT
      ...
    FROM table1
    INNER JOIN table2 
    ON (UCase(table1.name) LIKE '*' & UCase(Trim(table2.name)) & '*')

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by molona View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    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

  12. #12
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    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.

  13. #13
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    @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

  14. #14
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    @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:
    Code:
    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):
    Code:
    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. #15
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    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. #16
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    The results are not as expected though. It doesn't always gets it right and associates the right program.
    For example?

  17. #17
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    Following the Acrobat example, I get these results

    Code:
          
    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".

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

  18. #18
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    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. #19
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.

  20. #20
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by molona View Post
    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".

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    sounds like a job for DISTINCT ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by guido2004 View Post
    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.

  23. #23
    #titanic {float:none} silver trophy
    molona's Avatar
    Join Date
    Feb 2005
    Location
    from Madrid to Heaven
    Posts
    8,179
    Mentioned
    234 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    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"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •