I want to SELECT all rows from first_table
INNER JOIN second_table
for values where the column
in first_table
fall within range of values from two columns
in second_table
.
As example, I have created these two tables
First table:
CREATE TABLE location (loc_id int, gpstime int, lat_start float, lat_end float);
Second table:
CREATE TABLE segments( segment_id int, lat float,foot float,bike float,bus float,car float,metro float);
Then insert these values:
INSERT INTO location
SELECT 1, 12345, 41.10, 41.17 UNION ALL
SELECT 2, 99999, 42.00, 42.12 UNION ALL
SELECT 3,12346, 41.35, 41.30
INSERT INTO segments
SELECT 1, 41.15, 0, 0, 0.95, 0.025, 0 UNION ALL
SELECT 2, 42.19, 0.95, 0, 0, 0, 0.025 UNION ALL
SELECT 3, 41.33, 0,0, 0, 0, 1
The result set I want should return all rows for which value of column lat
in segments
table is a value that exists between lat_start
and lat_end
columns of location
table.
So I write the following query:
SELECT gpstime AS timestamp, lat_start, lat_end,
CASE
WHEN GREATEST(car, bike, bus, metro, foot) = car THEN 'car'
WHEN GREATEST(car, bike, bus, metro, foot) = bike THEN 'bike'
WHEN GREATEST(car, bike, bus, metro, foot) = bus THEN 'bus'
WHEN GREATEST(car, bike, bus, metro, foot) = metro THEN 'metro'
WHEN GREATEST(car, bike, bus, metro, foot) = foot THEN 'walking'
ELSE 'na' END AS mode
FROM location
INNER JOIN segments
ON lat BETWEEN lat_start AND lat_end;
Problem: The BETWEEN
key word returns TRUE only when check-value
fall in range (min, max)
and does not evaluate to (max, min)
. Clearly, the value of lat
in the "last" row of segments
(41.33) is a value that fall within the range of (lat_start, lat_end)
of location
(41.30-41.35). However, the BETWEEN clause excludes this row, as per returned the table below:
+-----------+-----------+---------+------+
| timestamp | lat_start | lat_end | mode |
+-----------+-----------+---------+------+
| 12345 | 41.1 | 41.17 | bus |
+-----------+-----------+---------+------+
How do I ensure all rows satisfying the criteria are returned?
Additional details
I posted this question yesterday, and following David answer, I was glad everything seems to be fixed. However, working on my database looks like there is still an error in the query as yet some rows are still missing. I reproduce minimal scenario of my work once again:
Table 1:
CREATE TABLE location (id int, lat float, lon float, gpstime int);
Table 2:
CREATE TABLE trips( trip_id int, lat_start float, lat_end float,
lon_start float, lon_end float,
travel_mode text);
Add rows:
INSERT INTO location
SELECT 1, 41.2, 8.3, 11111 UNION ALL --both lat and lon in the interval
SELECT 2, 43.3, 8.5, 22222 UNION ALL --only lat meets criteria
SELECT 3, 44.0, 8.5, 33333 --only lon meets criteria
INSERT INTO trips
SELECT 1, 41.1, 41.9, 8.0, 8.8, 'foot' UNION ALL
SELECT 2, 43.1, 43.5, 9.5, 8.9, 'bus' UNION ALL
SELECT 3, 42.1, 42.8, 8.7, 8.0, 'metro'
And then the query, using BETWEEN SYMMETRIC
:
SELECT trip_id, gpstime AS timestamp, lat, lon, travel_mode
FROM location
INNER JOIN trips
ON (lat BETWEEN SYMMETRIC lat_start AND lat_end)
AND (lon BETWEEN SYMMETRIC lon_start AND lon_end)
Results:
+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat | lon | travel_mode |
+---------+-----------+------+-----+-------------+
| 1 | 11111 | 41.2 | 8.3 | foot |
+---------+-----------+------+-----+-------------+
Yet, row 3
of location
table is not included in the result set. What I am missing here?
Note: Using BETWEEN SYMMETRIC
doesn't solve the problem yet. It appears value-checking is only applied to lat
column and NONE to lon
.
Db<>fiddle
I share with you, this db_fiddle I created to test the query. Cleary, all rows of location
table should be returned as rows
2 and 3 meet one criteria either.
JOIN .. ON table1.field1 BETWEEN table2.field2 AND table2.field3
must be used. Or the same using 2 unequiations. – Akina Sep 25 at 16:32segments
is never returned. – arilwan Sep 25 at 16:42location
is wrong,lat_start > lat_end
in it... normalize your data. Or use LEAST/GREATEST functions. – Akina Sep 25 at 16:54ON (lat BETWEEN lat_start AND lat_end) OR (lat BETWEEN lat_end AND lat_start)
(parentheses are redundant, only to make the code clear). – ypercubeᵀᴹ Sep 26 at 15:22between symmetric
instead - that doesn't care about the order of the edges – a_horse_with_no_name Sep 26 at 15:33