Mysql – Left outer join to itself to find outliers

join;MySQL

I have a table of item types and the coordinates of the items, like this:

    ItemType | X
    --------------
    1        | 1
    1        | 5
    2        | 5
    1        | 6
    2        | 6
    1        | 7
    2        | 7
    2        | 10

I need to find the count of outliers – the items that are not in the vicinity of the items of another type (there may be more than two types, I want to count the outliers for each pair of item types). The vicinity is defined, say, as abs(X1-X2)<=2. That way, the first and the last rows of the table are the outliers.

I tried to run the following query but did not get the expected answer (I expected to get 1 for the pair of 1 and 2 and another one for the pair of 2 and 1).

select t1.ItemType, t2.ItemType, count(t1.ItemType) 
from Tab as t1 
left outer join Tab as t2 on abs(t1.X-t2.X)<=2 
where t1.ItemType!=t2.ItemType 
and t2.ItemType is null 
group by t1.ItemType, t2.ItemType;

What am I doing wrong? I am using MySQL.

Best Answer

Try the following query. 

SELECT t1.ItemType, t2.Itemtype, COUNT(t1.X)
FROM Tab t1, Tab t2
WHERE
    t1.ItemType < t2.ItemType
    AND ABS(t1.X - t2.X) > 2
GROUP BY t1.ItemType, t2.Itemtype
;

It should produce the following results.

+----------+----------+-------------+
| ItemType | ItemType | COUNT(t1.X) |
+----------+----------+-------------+
|     1    |     2    |      7      |
+----------+----------+-------------+
Related Question