SQL Anywhere: find rows that are +-2 compared to another row

Question

I have the following table:

ID  User  Form  Depth
1   A     ABC   2001
1   A     XYZ   1001
1   B     XYZ   1003
1   B     DEF   3001
1   C     XYZ   1000

If ID and Form are identical, I need to identify those rows that are +-2 from User A. Using the example above, the script would return:

ID  User  Form  Depth
1   B     XYZ   1003
1   C     XYZ   1000

I already have a script which identifies rows with identical ID and Form--I just need the other part, but I'm struggling with figuring out the logic. I was hoping there was some kind of DIFF function I could use, but I can't find one for SQL Anywhere.

Does anyone have any suggestions?

Thanks!

Answer 1

If you're looking for the depth to be exactly +/-2 from A's depth:

select t1.*
from   mytab t1,
       mytab t2
where  t1.id    = t2.id
and    t1.form  = t2.form
and    t1.user != 'A'
and    t2.user  = 'A'
and    abs(t1.depth - t2.depth) = 2
go

ID  User  Form  Depth
--- ----- ----- -----
1   B     XYZ   1003

If you're looking for the depth to be within 2 of A's depth (ie, diff <= 2):

select t1.*
from   mytab t1,
       mytab t2
where  t1.id    = t2.id
and    t1.form  = t2.form
and    t1.user != 'A'
and    t2.user  = 'A'
and    abs(t1.depth - t2.depth) <= 2
go

ID  User  Form  Depth
--- ----- ----- -----
1   B     XYZ   1003
1   C     XYZ   1000

This is pretty basic SQL so while this fiddle was done with MySQL, you should find the queries work in SQLAnywhere, too: sql fiddle