#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2017
    Posts
    2
    Rep Power
    0

    Join on part of text col


    I have 2 tables

    Table1 - Jobs

    Code:
        _______________________
        |Id|ClientId|EndDate   |
        | 1|  3     |2017-03-01|
        | 2|  6     |2017-04-12|
        | 3|  13    |2017-06-23|
        |__|________|__________|
    Table2 - Files

    Code:
        ____________________________
        |Id|FileName|Path|DateSent  |
        |1 |Joe_59   |sent|2017-07-01|
        |2 |Jill_6  |sent|2017-07-01|
        |3 |Bob_13  |sent|2017-07-04|
        |__|________|____|__________|





    I have this query...

    Code:
        Select t.Id JobNum,t.ClientId Id,t.EndDate
        from jobs t
        inner join 
        (SELECT ClientId,MAX(EndDate) as EndDate
        FROM jobs
        group by ClientId
        )a
        on a.ClientId = t.ClientId and a.EndDate = t.EndDate
    The result is Jobnum,Id,EndDate
    I now want to get the filename from table 2 which matches the client id coming from table 1 . The file names each end with a number.

    eg. Joe_59
    The number is actualy the client id. So I want to join on this table to extract the file name where Id = the number which is part of the text in 'filename' column. So the final output should be..

    1,3,2017-03-01,Joe_59
    2,6,2017-04-12,Jill_6
    3,13,2017-06-23,Bob_13
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,333
    Rep Power
    9645
    Create a new column to hold just the client ID, fill it in with an UPDATE for the existing data, make sure it gets populated from now on for new data, then JOIN on that. It'll be much more efficient than having to extract the client ID every time you need it.

    To get just the ID use SUBSTRING_INDEX:
    Code:
    SUBSTRING_INDEX(FileName, "_", -1)

    Comments on this post

    • r937 agrees
    • EmDev agrees
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2017
    Posts
    2
    Rep Power
    0
    That was not really what I had planned but creating the extra column was easy to do and it has solved my problem. Thanks

IMN logo majestic logo threadwatch logo seochat tools logo