March 22, 2009 at 7:27 pm
Using SQL 2000 I'm trying to do a left join that has a table1 of data and a table2 that contains cost values with patterns that match data in the first. I'd like to match the first entry that matches. For example one entry might have a a__ with a final entry of %. If a value has the value "all" then I only want a single match.
An example of the statement would be:
SELECT * FROM table1 JOIN LEFT table2 ON table1.field like table2.field
Table2.field contains a list of patterns that are inserted in order. I would like to return the first match, so the rest of the row can generate a cost structure.
Thanks,
March 22, 2009 at 8:36 pm
Can you please provide statements for table create and insert of some example data?
Thanks
March 23, 2009 at 9:43 am
Here is a sample table structure and some sample data. If I run as is I would get two matches for the 1800 number, one free, and one that would be billed at a long distance rate.
CREATE TABLE Table1
(
originalCalledPartyNumber VARCHAR(50),
finalCalledPartyNumber VARCHAR(50),
duration INT,
origDeviceName VARCHAR(129),
)
CREATE TABLE Table2
(
Pattern VARCHAR(50),
minuterate VARCHAR(50),
)
Table1
1234,18005551212,145,4390228454
1234,18165551212,145,4390228435
Table2
1800%,0
1__________,0.03
March 23, 2009 at 9:55 am
I under stand that the 1800% would match to row 1 of your table1 in the example.
I'm unsure what the 1__________ will match to in table1 and Why?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 23, 2009 at 9:58 am
Is this the kind of thing you trying to do?
SELECT *
FROM Table1 a
INNER JOIN Table2 b
ONPATINDEX(b.Pattern,finalCalledPartyNumber) != 0
Not the best solution yet, cause you will get no index usage because of the on Clause but we can change the code once I work out exactly what you need 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 23, 2009 at 10:01 am
The 1__________ will match because the format of a long distance number 1 followed by 10 underscores is the same as an 1800 number format. My idea is to build the table so it matches top to bottom and the patterns are most specific to least specific. I'll try what you provided as see if it works.
March 23, 2009 at 12:13 pm
I tried the code specified, and it worked like my prior code where it returns every match. So every tool free number returns once as a free call and once as a long distance call.
I'm been able to acomplish what I wanted by specifing the patterns very speciifcally, but as I need to report on more international in the future and that is going to really difficult to maintain if I can't do a single match in order.
March 24, 2009 at 3:14 am
ok cool,
but how do you determine priority of minuterate?
There is no idea or order by in that table?
or will the 1800% always take preference over 1_______?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 24, 2009 at 7:22 am
I have been attempting based on the order of the rows in the second table. I could add another column as an index number to set the order if that would make it work better.
March 27, 2009 at 11:46 am
I found the solution. I added an order index field to the table and in the join left command used the max() parameter.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply