displaying related data from a table

[iamready]

Dear All,
I am using a SQL server database with around 20 columns,all the columns have numeric values, I want to write an SQL statement which does the following:

compare each row of the table with all other rows in the table and return all the rows that have a difference of + or - 0.5 in each column, for eg if row1 has values 12.2,13.6,11.4,15.7 corresponding to column1,2,3,4 the sql statement should return all the rows from the table with values of column 1-4 between

12.2- 0.5 to 12.2 + 0.5
13.6 -0.5 to 13.6+ 0.5
11.4 -0.5 to 11.4 +0.5
15.7 -0.5 to 15.7 +0.5


Could anyone suggest how i go about doing this.

thank you in advance
harsha

 

[JarlH]

SELECT t2.* FROM tab t1, tab t2
WHERE ABS(t1.c1 - t2.c1) <= 0.5
AND ABS(t1.c2 - t2.c2) <= 0.5
...
AND ABS(t1.c20 - t2.c20) <= 0.5

 

[PHV]

JarlH, add this:
AND t2.PK<>t1.PK
to avoid a row matches itself

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[iamready]

Dear PHV and JarlH,

there is only one table, we are comparing one row with another in the same table, so do i put something like


SELECT t1.*,t2.* FROM tab t3

instead of

SELECT t2.* FROM tab t1, tab t2

would it work?

thank you
harsha

 

[PHV]

would it work?
No and you'll raise syntax error.
Why not trying the JarlH suggestion + the test on primary key shoulding be different ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JarlH]

iamready doesn't mention any pk column.

Besides, if you add that primary key test, and no row is similar to another row, no rows at all will be returned.

If you leave that pk test, every row in the table will be returned, and for each row, the similar ones will be returned. Just what's asked for.

 

[PHV]

My apologies JarlH, I didn't realize iamready wanted all the rows anyway.

 

[iamready]

thanks guys the program worked, appreciate your help.


harsha