Select for items meeting both of two criteria in SQL

Question

I have a system that reports the XYZ location of items in a field to a SQL database. I am attempting to filter for false positives (only identifying moving items) by filtering for a known point in time when items pass a point, and at what location the items should be in after passing a point.

My logic is if an item is in one location and time AND in another location and time, it must have moved.

so I have this query:

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 realize what I am asking SQL with the above query is for something in two places at once (which cannot happen), but what I want are records which exist in both these spatial constraints - I want records for when they were in both, not to ask for a record that is in both at the same time.

I can help you out, but first, how do you know that the item you find with the second part of the query is the same item in the first part?? How do you know what time/location to query on? — Charleh, Jan 29 '13 at 17:46
I have a physical sensor that detects the presence and leaving of the item past a line in space. that is what I use to establish a known physical location. I am using SQL YOG to do this — Uberbot7147, Jan 29 '13 at 19:04

score 2 · Accepted Answer · answered Jan 29 '13 at 17:46

I think you need to join on itself -- your current query will never produce any results.

Try something like this to get the tags that have multiple occurences in your WHERE Critera -- however, are you missing some criteria on your 2nd location? This example uses your above 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.

For the second location, I'm thinking if they were located in a time before the item entered the range (I have a line sensor beam that detects presence and leaving a field) && in the area before it that makes a valid criterion. — Uberbot7147, Jan 29 '13 at 19:16
I'm getting 22 tags returned by this, which is something I might expect. thanks. — Uberbot7147, Jan 29 '13 at 19:35

score 1 · Answer 2 · answered Jan 29 '13 at 18:41

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 self-join approach above is also good; you could also look into derived tables.

I'm thinking this is a right track, but I think I would need two "having" bound boxes to select for only moving tags? (executing your query above as is produces no results, nor does it with mini and max ranges entered) I'm just not sure what the concept of operation aggregation uses on first glance, but will research it. Thanks for the answer. — Uberbot7147, Jan 29 '13 at 19:32

Select for items meeting both of two criteria in SQL

2 Answers2