Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Query/ Innerjoin help

    Hi,
    new to this forum and would like help please.

    I'm trying to do a query and need help on a innerjoin.

    The values of one record on each of the key tables are something like this
    pb_amount.file = "greatsong-640k"
    stream_videoFile.hlsFilename = "greatsong-playlist.m3u8"

    so in the code below, there is no match. but if you ignore the last 5 characters of "pb_amount.file" and ignore the last 14 characters of
    "stream_videoFile.hlsFilename" you will match on the value "greatsong"

    How would I do this? do a calculated field? if so, could someone give me some hints here? I would really appreciate...


    Code:
    SELECT
    pb_amount.id,
    pb_amount.file,
    pb_amount.sum,
    pb_amount.mp3Size,
    stream_videoFile.id,
    stream_videoFile.ContentID,
    stream_videoFile.hlsFilename
    FROM
    pb_amount
    INNER JOIN stream_videoFile ON pb_amount.file = stream_videoFile.hlsFilename
    Last edited by rgreen; 03-24-12 at 17:39.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all,
    did the query returned no error?
    Because, there was no stream_videoFile as table name nor alias.

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    sorry, my bad copy and paste. I fixed it above. the table is stream_videoFile

    thank you. rg

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by rgreen View Post
    pb_amount.file = "greatsong-640k"
    stream_videoFile.hlsFilename = "greatsong-playlist.m3u8"

    so in the code below, there is no match. but if you ignore the last 5 characters of "pb_amount.file" and ignore the last 14 characters of
    "stream_videoFile.hlsFilename" you will match on the value "greatsong"
    You need to use a substr function to extract beginning characters of strings.
    Do all fields contains the minus '-' character ?
    If yes, you can extract characters from strings from the beginning to a postioon of '-' within strings:
    Code:
    mysql> select substr( 'greatsong-playlist.m3u8', 1, locate( '-', 'greatsong-playlist.m3u8' ));
    +---------------------------------------------------------------------------------+
    | substr( 'greatsong-playlist.m3u8', 1, locate( '-', 'greatsong-playlist.m3u8' )) |
    +---------------------------------------------------------------------------------+
    | greatsong-                                                                      |
    +---------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    so your join would look like:
    Code:
    INNER JOIN stream_videoFile 
    ON substr( pb_amount.file, 1, Locate( '-', pb_amount.file))  
     = substr( stream_videoFile.hlsFilename, 1, Locate( '-', stream_videoFile.hlsFilename ))

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your requirements are not evenly clear for me.

    If there were multiple rows mathed first n characters and m characters and so on..., what result do you want?

    For example:
    What result do you want from the following data?

    Code:
    SELECT * FROM pb_amount;
    ------------------------------------------------------------------------------
    
    ID     FILE            SUM         MP3SIZE    
    ------ --------------- ----------- -----------
         1 greatsong-640k          100         100
         2 greatsong-plot           70          20
         3 greatsong-plan           50          10
         4 great-song-list         200         150
    
      4 record(s) selected.
    Code:
    SELECT * FROM stream_videoFile;
    ------------------------------------------------------------------------------
    
    ID     CONTENTID   HLSFILENAME            
    ------ ----------- -----------------------
        71           7 greatsong-playlist.m3u8
        72           8 great-song-list.m3u8   
        73           9 greensong-playlist.m3u8
    
      3 record(s) selected.
    If these data were not practical,
    please publish practical sample data and expected results from the data.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a result returned number of matched characters and matched string for each combination of rows in pb_amount and stream_videoFile.

    Code:
    PA_ID  FILE            SV_ID  HLSFILENAME             MATCH  MATCHED_STRING 
    ------ --------------- ------ ----------------------- ------ ---------------
         1 greatsong-640k      71 greatsong-playlist.m3u8     10 greatsong-     
         1 greatsong-640k      72 great-song-list.m3u8         5 great          
         1 greatsong-640k      73 greensong-playlist.m3u8      3 gre            
         2 greatsong-plot      71 greatsong-playlist.m3u8     12 greatsong-pl   
         2 greatsong-plot      72 great-song-list.m3u8         5 great          
         2 greatsong-plot      73 greensong-playlist.m3u8      3 gre            
         3 greatsong-plan      71 greatsong-playlist.m3u8     13 greatsong-pla  
         3 greatsong-plan      72 great-song-list.m3u8         5 great          
         3 greatsong-plan      73 greensong-playlist.m3u8      3 gre            
         4 great-song-list     72 great-song-list.m3u8        15 great-song-list
         4 great-song-list     71 greatsong-playlist.m3u8      5 great          
         4 great-song-list     73 greensong-playlist.m3u8      3 gre

  7. #7
    Join Date
    Mar 2004
    Posts
    480
    Quote Originally Posted by kordirko View Post
    Do all fields contains the minus '-' character ?
    If yes, you can extract characters from strings from the beginning to a postioon of '-' within strings:
    If they all contain the dash or minus character and you only want to match on the info to the left of the '-' then look at SUBSTRING_INDEX in the manual. A little easier to use than the substring as used by kordirko.

  8. #8
    Join Date
    Mar 2012
    Posts
    3
    thanks for all the help. I should have been a bit more clear - I always try to cut out what is unnecessary to solve my problem and I probably left out too much. it's actually an interesting issue. I am parsing and creating summaries of video logs. and the stream_videoFile.hlsFilename is the source video url "greatsong-playlist.m3u8" and each video url has 4 different video bitrate variations "pb_amount.file" appearing on the logs that all end in different bitrates - so the reason for -640 , or -128, etc. I need to "link" the different bitrates to a single video url. I am now going to create a sum for the field "sum" per video url, which will account for all the different bitrates.

    I ended up using this

    Code:
    LEFT OUTER JOIN stream_videoFile ON left(pb_amount.file,(length(pb_amount.file)-5)) = left(stream_videoFile.hlsFilename,(length(stream_videoFile.hlsFilename)-14))
    and it worked fine. I'm concerned that some of the video filenames might have a "-" in the name which would throw the substr function.

    thanks for the help... rg

Posting Permissions

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