Select for items that meet two criteria in SQL
I have a system that reports the location of XYZ items in a SQL database field. I'm trying to filter out false positives (only identifying moving elements) by filtering for a known point in time when the elements pass the point, and where the elements should be after passing the point.
My logic is if an element is in one place and time AND in another place and time, it should be moved.
so I have this request:
SELECT tag_ID, X_location*3.28, Y_location*3.28, locate_time
FROM tag_blink_history
WHERE
(LOCATE_TIME > '2013-01-29 11:05:51'
AND LOCATE_TIME < '2013-01-29 11:06:56'
AND ((y_location*3.28 > 61)
AND (y_location*3.28 < 67.5))
AND ((x_location*3.28 > 14.5)
AND (x_location*3.28 < 17.5)))
AND (((y_location*3.28 > 70)
AND (y_location*3.28 < 75))
AND locate_time < '2013-01-29 11:06:50' )
ORDER BY tag_id DESC
Any ideas? I understand that I am asking SQL with the above query, for something in two places at once (which cannot be), but what I want is records that exist in both of these spatial constraints - I need records when they are were in both, rather than requesting a record that is in both at the same time.
source to share
I think you need to join yourself - your current query will never return any results.
Try something like this to get tags that have multiple occurrences in your WHERE Critera - however, are you missing some criteria in your second place? This example uses your example:
SELECT DISTINCT t.tag_ID
FROM tag_blink_history t
JOIN tag_blink_history t2 ON t.tag_ID = t2.tag_ID AND (t.x_location <> t2.x_location OR t.y_location <> t2.y_location)
WHERE (t.LOCATE_TIME > '2013-01-29 11:05:51'
AND t.LOCATE_TIME < '2013-01-29 11:06:56'
AND ((t.y_location*3.28 > 61) AND (t.y_location*3.28 < 67.5))
AND ((t.x_location*3.28 > 14.5) AND (t.x_location*3.28 < 17.5)))
AND (((t2.y_location*3.28 > 70) AND (t2.y_location*3.28 < 75))
AND t2.locate_time < '2013-01-29 11:06:50' )
ORDER BY t.tag_id DESC
Good luck.
source to share
You can use aggregation (one of several approaches):
SELECT tag_ID, min(X_location*3.28), max(X_location*3.28), min(Y_location*3.28), max(Y_location*3.28), min(locate_time), max(locate_time)
FROM tag_blink_history
WHERE
(LOCATE_TIME > '2013-01-29 11:05:51'
AND LOCATE_TIME < '2013-01-29 11:06:56'
AND ((y_location*3.28 > 61)
AND (y_location*3.28 < 67.5))
AND ((x_location*3.28 > 14.5)
AND (x_location*3.28 < 17.5)))
AND (((y_location*3.28 > 70)
AND (y_location*3.28 < 75))
AND locate_time < '2013-01-29 11:06:50' )
GROUP BY
tag_ID
HAVING
(min(X_location) <> max(X_location))
OR
(min(Y_location) <> max(Y_location))
ORDER BY tag_id DESC
@sgeddes the self-connection method above is also good; you can also explore derived tables.
source to share