none
JET SQL: Join two tables on different columns of each table which I had to use string manipulation to achieve the same format for comparison

    Question

  •     TABLE dbo_R5PERSONNEL
        column PER_DESC
        Do, John
        Jones, Jacky
       
        TABLE dbo_R5USERS
        column USR_DESC
        John Do
        Jack Jones

    Q: How would I join these two tables to get all the USR_DESC from Table dbo_R5USERS that exist in column PER_DESC in Table dbo_R5PERSONNEL.

    I used string manipulation to capitalize and extract just the last names from each table

        SELECT UCASE(MID(TRIM(dbo_R5USERS.USR_DESC),  INSTR(TRIM(dbo_R5USERS.USR_DESC), ' ') + 1, LEN(TRIM(dbo_R5USERS.USR_DESC)) - INSTR(TRIM(dbo_R5USERS.USR_DESC), ' '))) AS LastName
        FROM dbo_R5USERS
        WHERE dbo_R5USERS.USR_ACTIVE = '+'
        ORDER BY dbo_R5USERS.USR_DESC;


        SELECT UCASE(MID(TRIM(dbo_R5PERSONNEL.PER_DESC),1,INSTR(TRIM(dbo_R5PERSONNEL.PER_DESC),',')-1)) AS LastName
        FROM dbo_R5PERSONNEL
        WHERE dbo_R5PERSONNEL.PER_NOTUSED='+'
        ORDER BY dbo_R5PERSONNEL.PER_DESC;

    How would I incorporate a JOIN on these two subqueries? Is there another method to achieve my desired results?

    I tried this but it produces an error that state: Syntax error in JOIN operation

    SELECT UCASE(MID(TRIM(dbo_R5USERS.USR_DESC), INSTR(TRIM(dbo_R5USERS.USR_DESC), ' ') + 1, LEN(TRIM(dbo_R5USERS.USR_DESC)) - INSTR(TRIM(dbo_R5USERS.USR_DESC), ' '))) AS LastName FROM dbo_R5USERS INNER JOIN (SELECT UCASE(MID(TRIM(dbo_R5PERSONNEL.PER_DESC),1,INSTR(TRIM(dbo_R5PERSONNEL.PER_DESC),‌​',')-1)) AS LastName FROM dbo_R5PERSONNEL WHERE dbo_R5PERSONNEL.PER_NOTUSED='+') ON (dbo_R5USERS.LastName = dbo_R5PERSONNEL.LastName) WHERE dbo_R5USERS.USR_ACTIVE = '+'

    Thanks in advance!

    • Moved by Naomi N Friday, September 28, 2012 6:41 PM Better answer can be here (From:Transact-SQL)
    Friday, September 28, 2012 4:38 PM

Answers

  • Try to build a query with the different pieces before using them in a join or criteria.

    For example:

    TABLE dbo_R5PERSONNEL
        column PER_DESC
        Do, John
        Jones, Jacky

    Build the query:

    SELECT PER_DESC, MID(PER_DESC, INSTR(PER_DESC, ', ') + 2, 1) AS First_Initial, LEFT(PER_DESC, INSTR(PER_DESC, ', ') - 1) AS Last_Name

    FROM dbo_R5PERSONNEL

    If this doesn't work and you are sure you have the syntax correct, then there likely is a problem where the data is not in the expected format (i.e. Last Name, First Name).  Lack of data (e.g. no First Name) also means that the data does not meet the expected format.  Most of the time, I find it necessary to scan and test the data to try and understand what assumptions can be made.

    • Marked as answer by vkl3 Thursday, October 04, 2012 6:20 PM
    Friday, September 28, 2012 11:28 PM
  • It has not been mentioned in the Topic so far but IMHO, the correct solution is to re-structure both Tables to use 2 Fields to store LastName and FirstName separately instead of 1 Field.  Clearly for your requirement as posted (there will be other similar requirements in the future) and the way you need to manipulate the data, the current Field store 2 distinct data items per Field value which violates the First Normal Form (Field value must be atomic, i.e. A field value must store 1 single item of data, not a combination of data items).

    If you don't have the option to modify the Tables, then you just have to find work-arounds. However, the work-arounds, if found, will be very much less efficient than what you can do with the correctly-structured Tables.

    For the current problem, I would try to make the SQL less cluttersome by creating 2 UDFs to massage the Field values from the 2 Tables into a common format for comparison.  You can then use the UDFs for the Join or the explicit WHERE conditions in your Query.  This way, you can test the conversions independent from the SQL to aid with the debugging also. 

    AFAICS, both methods (embedding the String manipulations in the SQL and separating the String manipulations out using the UDFs) are both equally much less efficient than the correctly structured Tables so I ignore the efficiency considerations in this case.

    P.S. Have you considered using a Pass-Through Query where you can use Naomi's T-SQL rather than an Access Query/JET SQL?  This is certainly more efficient than the Access Query/JET SQL because the Pass-Through Query is processed by the Microsoft SQL Server, not your Access client (Front-End) and only the matched/selected data is returned to your Access Front-End.


    Van Dinh









    • Edited by Van DinhMVP Saturday, September 29, 2012 3:30 AM Typos
    • Proposed as answer by Naomi N Sunday, September 30, 2012 1:57 AM
    • Unproposed as answer by vkl3 Monday, October 01, 2012 11:37 PM
    • Marked as answer by vkl3 Monday, October 01, 2012 11:38 PM
    Saturday, September 29, 2012 3:08 AM

All replies

  • I assume that this may work:

    SELECT R.LastName, P.LastName FROM (SELECT UCASE(MID(TRIM(dbo_R5USERS.USR_DESC), INSTR(TRIM(dbo_R5USERS.USR_DESC), ' ') + 1,

    LEN(TRIM(dbo_R5USERS.USR_DESC)) - INSTR(TRIM(dbo_R5USERS.USR_DESC), ' '))) AS LastName FROM dbo_R5USERS WHERE dbo_R5USERS.USR_ACTIVE = '+') R INNER JOIN -- if we want to only get matching users ( SELECT UCASE(MID(TRIM(dbo_R5PERSONNEL.PER_DESC),1,INSTR(TRIM(dbo_R5PERSONNEL.PER_DESC),',')-1)) AS LastName FROM dbo_R5PERSONNEL WHERE dbo_R5PERSONNEL.PER_NOTUSED='+') P ON R.LastName = P.LastName



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



    • Edited by Naomi N Friday, September 28, 2012 5:07 PM
    Friday, September 28, 2012 5:06 PM
  • Hi Naomi,

    Thanks for your reply. When I try to run that I get the following error: Invalid length parameter passed to the LEFT or SUBSTRING function. That is odd because when I run the 2 select statements individually, it works. Any ideas?

    Thanks

    Friday, September 28, 2012 6:09 PM
  • I don't see LEN or SUBSTRING function in the above. Are you using SQL Server or Access?

    Usually this error means you didn't consider one of the conditions and it causes negative value to be passed to SUBSTRING.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, September 28, 2012 6:18 PM
  • I am using Access.
    Friday, September 28, 2012 6:24 PM
  • The error message is a bit misleading then. I am going to move this into Access forum then, here we're dealing with T-SQL.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, September 28, 2012 6:41 PM
  • Create a new query and go into SQL design mode (as opposed to graphical design mode).  Try the following:

    SELECT u.* FROM dbo_R5USERS u INNER JOIN dbo_R5PERSONNEL p ON

    u.USR_DESC = MID(p.PER_DESC, INSTR(p.PER_DESC, ', ') + 2) & ' ' & LEFT(p.PER_DESC, INSTR(p.PER_DESC, ', ') - 1)

    You might have to tweak it but I think you get the idea.

    Friday, September 28, 2012 8:43 PM
  • Hi Kech,

    Thanks for the help. Due to the format of the names, I need to convert the values in column PER_DESC in table R5PERSONNEL to JDO, JJONES to reliably compare it to a column USR_CODE in table R5USERS. As you can see in the tables below, the values in USR_CODE can be cut off by a single or couple of characters. So, rather than displaying JJONES, the value is JJONE. Is there a comparison I can use to match it with the converted values of cloumn PER_DESC in table R5PERSONNEL? Would 'IN' or 'LIKE' work? See below for my sample code that produces an error: Missing ), ], or Item in query expression 'u.USR_CODE= UCASE(MID(TRIM(p.PER_DESC),INSTR(TRIM(p.PER_DESC),',')-1,1) & MID(TRIM(p.PER_DESC),1,INSTR(TRIM(p.PER_DESC),',')-1'

    TABLE dbo_R5PERSONNEL
        column PER_DESC      
        Do, John
        Jones, Jacky

    TABLE dbo_R5PERSONNEL
        column PER_DESC       
       JDO 
        JJONES

        TABLE dbo_R5USERS
        column USR_CODE
        JDO
        JJONE

    SELECT u.* FROM dbo_R5USERS u INNER JOIN dbo_R5PERSONNEL p ON
    u.USR_CODE= UCASE(MID(TRIM(p.PER_DESC),INSTR(TRIM(p.PER_DESC),',')-1,1) & MID(TRIM(p.PER_DESC),1,INSTR(TRIM(p.PER_DESC),',')-1)

    When I remove UCASE(), I get the error: Invalid procedure Call

    SELECT u.* FROM dbo_R5USERS u INNER JOIN dbo_R5PERSONNEL p ON
    u.USR_CODE = MID(TRIM(p.PER_DESC),INSTR(TRIM(p.PER_DESC),',')-1,1) & MID(TRIM(p.PER_DESC),1,INSTR(TRIM(p.PER_DESC),',')-1)

    Thanks!


    • Edited by vkl3 Friday, September 28, 2012 10:48 PM
    Friday, September 28, 2012 10:31 PM
  • The UCASE() function should not be necessary and you need to confirm whether PER_DESC is recorded with a comma or a comma and a space between the two parts of the name.  If you do need to use the TRIM() function, put it outside each segment so that it isn't run multiple times unnecessarily.  In other words, TRIM() only affects the ends of the string, and so it doesn't affect INSTR() when you are searching for something other than a space.  Try the following:

    SELECT u.* FROM dbo_R5USERS u INNER JOIN dbo_R5PERSONNEL p ON
    u.USR_CODE = MID(p.PER_DESC, INSTR(p.PER_DESC, ', ') + 2, 1) & LEFT(p.PER_DESC, INSTR(p.PER_DESC, ', ') - 1)

    However, unless you have conditioned the data in some way, the comparison will not be reliable.  For example, the join will be incorrect for the following people if the last name is truncated after the 4th character in dbo_R5USERS:

    John Rich

    John Richards

    John Richardson

    Friday, September 28, 2012 11:04 PM
  • Not sure why but I get an error: Invalid procedure call when I execute your code. Any more ideas?
    There is a comma and a space between the 2 parts of the name in PER_DESC, so your code is correct in producing RJohn. Thanks!
    • Edited by vkl3 Friday, September 28, 2012 11:17 PM
    Friday, September 28, 2012 11:13 PM
  • Try to build a query with the different pieces before using them in a join or criteria.

    For example:

    TABLE dbo_R5PERSONNEL
        column PER_DESC
        Do, John
        Jones, Jacky

    Build the query:

    SELECT PER_DESC, MID(PER_DESC, INSTR(PER_DESC, ', ') + 2, 1) AS First_Initial, LEFT(PER_DESC, INSTR(PER_DESC, ', ') - 1) AS Last_Name

    FROM dbo_R5PERSONNEL

    If this doesn't work and you are sure you have the syntax correct, then there likely is a problem where the data is not in the expected format (i.e. Last Name, First Name).  Lack of data (e.g. no First Name) also means that the data does not meet the expected format.  Most of the time, I find it necessary to scan and test the data to try and understand what assumptions can be made.

    • Marked as answer by vkl3 Thursday, October 04, 2012 6:20 PM
    Friday, September 28, 2012 11:28 PM
  • I will try that after the weekend when I get back to work. I will keep you updated. Thanks a lot!
    Friday, September 28, 2012 11:31 PM
  • It has not been mentioned in the Topic so far but IMHO, the correct solution is to re-structure both Tables to use 2 Fields to store LastName and FirstName separately instead of 1 Field.  Clearly for your requirement as posted (there will be other similar requirements in the future) and the way you need to manipulate the data, the current Field store 2 distinct data items per Field value which violates the First Normal Form (Field value must be atomic, i.e. A field value must store 1 single item of data, not a combination of data items).

    If you don't have the option to modify the Tables, then you just have to find work-arounds. However, the work-arounds, if found, will be very much less efficient than what you can do with the correctly-structured Tables.

    For the current problem, I would try to make the SQL less cluttersome by creating 2 UDFs to massage the Field values from the 2 Tables into a common format for comparison.  You can then use the UDFs for the Join or the explicit WHERE conditions in your Query.  This way, you can test the conversions independent from the SQL to aid with the debugging also. 

    AFAICS, both methods (embedding the String manipulations in the SQL and separating the String manipulations out using the UDFs) are both equally much less efficient than the correctly structured Tables so I ignore the efficiency considerations in this case.

    P.S. Have you considered using a Pass-Through Query where you can use Naomi's T-SQL rather than an Access Query/JET SQL?  This is certainly more efficient than the Access Query/JET SQL because the Pass-Through Query is processed by the Microsoft SQL Server, not your Access client (Front-End) and only the matched/selected data is returned to your Access Front-End.


    Van Dinh









    • Edited by Van DinhMVP Saturday, September 29, 2012 3:30 AM Typos
    • Proposed as answer by Naomi N Sunday, September 30, 2012 1:57 AM
    • Unproposed as answer by vkl3 Monday, October 01, 2012 11:37 PM
    • Marked as answer by vkl3 Monday, October 01, 2012 11:38 PM
    Saturday, September 29, 2012 3:08 AM
  • Hello Everyone,

    I also agree that the tables should be restructured, but I do not have control over this. So, therefore I used all your recommendations (pass-through queries and T-SQL) and I came up with the following solution:

    SELECT U.LASTNAME AS USERLNAME, P.PERSONNELLNAME, P.PER_DESC, P.PER_NOTUSED, U.USR_DESC, U.USR_ACTIVE 
    FROM
    (SELECT UPPER(SUBSTRING(R5USERS.USR_DESC, 1 ,2) + SUBSTRING(R5USERS.USR_DESC, CHARINDEX(' ', R5USERS.USR_DESC) + 1, LEN(R5USERS.USR_DESC) - CHARINDEX(' ', R5USERS.USR_DESC))) AS LASTNAME, R5USERS.USR_DESC, R5USERS.USR_ACTIVE 
    FROM R5USERS
    WHERE R5USERS.USR_ACTIVE = '+') U
    INNER JOIN
    (SELECT UPPER(SUBSTRING(R5PERSONNEL.PER_DESC,CHARINDEX(', ', R5PERSONNEL.PER_DESC) + 2, 2) + SUBSTRING(R5PERSONNEL.PER_DESC, 0, CHARINDEX(', ', R5PERSONNEL.PER_DESC))) AS PERSONNELLNAME, R5PERSONNEL.PER_DESC, R5PERSONNEL.PER_NOTUSED 
        FROM R5PERSONNEL
        WHERE R5PERSONNEL.PER_NOTUSED='+') P
    ON U.LASTNAME = P.PERSONNELLNAME
    Thanks Again!
    Thursday, October 04, 2012 6:23 PM
  • Congratulations... Well-done...


    Van Dinh

    Thursday, October 04, 2012 10:37 PM