StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Inequality joins returning cartesian product
text
Body
copied!<p>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:</p> <pre><code>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 </code></pre> <p>And my query would be:</p> <pre><code>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 </code></pre> <p>And what I get looks like this:</p> <pre><code>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 </code></pre> <p>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:</p> <pre><code>ID_1Start End Name Time_1 Time_2 137 3:00 4:00 Omnia 3:10 3:57 </code></pre> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload