SQL Inequality joins returning cartesian product

Question

I have a problem getting join conditions to isolate unique records. My query is returning cartesian products, and I don't know how to make it stop. My tables look like this:

Table A         
ID_1    Start   End Name
137 1:00    2:00    Galia
137 2:00    3:00    Est
137 3:00    4:00    Omnia
137 4:00    5:00    Divisa
137 5:00    6:00    Partes
137 6:00    7:00    Tres
137 7:00    8:00    Quarum
137 8:00    9:00    Unam
137 9:00    10:00   Incolunt

Table B     
ID_1Time_1  Time_2
137 3:10    3:57

And my query would be:

select A.*, B.Time_1, B.Time_2
from Table_A A
inner join
Table_B B
on 
A.ID_1 = B.ID_1 and B.Time_1<=A.End and B.Time_2 >= A.Start

And what I get looks like this:

ID_1Start   End Name    Time_1  Time_2
137 1:00    2:00    Galia   3:10    3:57
137 2:00    3:00    Est 3:10    3:57
137 3:00    4:00    Omnia   3:10    3:57
137 4:00    5:00    Divisa  3:10    3:57
137 5:00    6:00    Partes  3:10    3:57
137 6:00    7:00    Tres    3:10    3:57
137 7:00    8:00    Quarum  3:10    3:57
137 8:00    9:00    Unam    3:10    3:57
137 9:00    10:00   Incolunt3:10    3:57

So it looks like it's giving the cartesian product of the two tables, which makes sense given that all three conditions are met for each record. What I want is for only the record where the times correspond to be returned, like this:

ID_1Start   End Name    Time_1  Time_2
137 3:00    4:00    Omnia   3:10    3:57

Any advice on how to structure the join to achieve this? I'm working on a Netezza box if that helps with available functionality. Thanks.

Answer 1

You want the following conditions to apply:

    A.Start <= B.Time_1 <= B.Time_2 <= A.End

Assuming that the column datatypes are coherent (ie. only storing times, not datetimes or timestamps), and the values are coherent (ie. the central predicate in the multirelation above is already valid). These conditions can be rewritten in SQL as

B.Time_2 <= A.End AND B.Time_1 >= A.Start

and not

B.Time_1 <= A.End and B.Time_2 >= A.Start

which could correspond to

B.Time_1 <= A.Start <= A.End <= B.Time_2

Hence:

select A.*, B.Time_1, B.Time_2
from Table_A A
inner join
Table_B B
on 
A.ID_1 = B.ID_1 and B.Time_1>=A.Start and B.Time_2 <= A.End