February 21, 2009 at 8:02 pm
I have the following tables.
TABLE1 has a row that store value of SF098* in field F1 and a value of ZK* in field F2. The values represent a range of values between SF098* to ZK* (one of the possible value is SF099).
In TABLE2 the first row has a value of AK* in field F1 and a value of ZS* in field F2. The values in these fields also represent a range of values between AK* to ZS* (of course one of the value would be SF099).
By visually looking at the ranges, I can make my determination that the rows in the 2 tables match. But I want to do this in a SQL query and output the match result. Can this be done?
Thanks
February 21, 2009 at 9:11 pm
Yes, but will have to define what you mean by match. Do you mean that the first range is completely contained by the second or do you mean that the first range overlaps (partly or completely) the second range?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2009 at 9:20 pm
Thanks for your response.
I was thinking about partial matching.
As long as a single value of the range in Table1 is within the range of the Table2, it is a match.
However, if getting a partial and full match requires 2 different ways of writing a query, I would like to know as well. Thanks again for your help
February 21, 2009 at 9:27 pm
And how are rows to be joined between the two tables? Do you want every row from Table1 that has any overlap to any row from Table2? Or is there a more specific way of joining them together?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2009 at 9:31 pm
Yes, I want any row from Table1 that has an overlap with any row in Table2.
Thank you so much..
February 21, 2009 at 10:15 pm
Like this:
Select F1, F2
From TABLE1 t1
Where EXISTS(Select * From TABLE2 t2
Where t1.F1 Between t2.F1 and t2.F2
And t1.F2 Between t2.F1 and t2.F2)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply