July 23rd, 2014, 07:08 AM
This is a tough one.
i have one table t1 (created through multiple joins, but let's not get into that) which contains
user | pattern1 | cost
i'm trying to join this with a different table t2 which contains
group | pattern2 | user | priority
the users are the same and i can join on t1.users=t2.users. the tricky part is joining on the pattern because some of the strings in pattern2 are longer than those in pattern1 and vice versa. I'm trying to match pattern2 to the closest string in pattern1 which is the same length or shorter.
since t1 is the results of a pretty heavy query, i would very much like to run it only once
i.e. i don't want to do anything like
Thanks in advance
July 23rd, 2014, 11:32 AM
could you show us some represenative strings in both tables please, some that will match and some that won't
having a hard time figuring out what you mean by "closest"
July 24th, 2014, 01:37 AM
sorry for being unclear.
the patterns are numeric.
here are some examples
pattern1: 123, 456, 457, 45678, 4567890454, 90
pattern2: 12345, 456789, 90
ideally the join should match:
12345 to 123
456789 to 45678 only
90 to 90
July 24th, 2014, 04:06 AM
why wouldn't 456789 match 4567890454?
Originally Posted by bobert123
why wouldn't 456789 match 456?
why wouldn't 90 also match 4567890454?
Originally Posted by bobert123
Last edited by r937; July 24th, 2014 at 04:08 AM.
July 24th, 2014, 04:37 AM
As I wrote earlier
July 24th, 2014, 04:39 AM
and i'm trying to match left to right. sorry if that wasn't clear
July 24th, 2014, 05:00 AM
Maybe you are looking for something like this:
How to like two columns in one SQL statement - Stack Overflow
Next step would then be to get the closest string. (Do you actually mean to match the shortest match?)
Maybe an possible solution would be to sum the length of the two pattern, group by userid and then take the row for each userid with the row/record having the shortest (MIN) number of length?
Last edited by MrFujin; July 24th, 2014 at 05:05 AM.
July 24th, 2014, 05:06 AM
thanks but i am trying to find the longest match, not the shortest. i can try to use the max length for that, which is a good idea. do you have any idea what the syntax of that would look like.
July 24th, 2014, 05:14 AM
You should really answer the questions by r937.
You want to get pattern 1 and what it match in pattern 2, e.g.
123 is contained/matched to 12345
45678 is contained/matched into 456789
90 is contained/matched into 90
456 no match because of 45678 is a longer match to 456789
457 no match to pattern 2
4567890454 no match to pattern 2
July 24th, 2014, 11:01 AM
Hi Mr Fujin, Apologies. I throught that my answer to R937 was clear.
correct, this is what I am looking for. any suggestions for syntax?
July 24th, 2014, 12:31 PM
The syntax for the two column match can be seen in the link I provided in previous post.
This will give you a list of all match for each user and pattern2.
If pattern 2 for one user match two pattern 1, you want to the one with MAX (CHAR_)LENGTH().
This will need group by on the user and pattern 2.
Try take a look at this site:
How to select the first/least/max row per group in SQL | Xaprb
I am not that skilled to give a combined valid SQL syntax for this.
Comments on this post
July 30th, 2014, 03:15 AM
thanks. this is very helpful