need help with LEFT JOIN statemen

[eurolinux]

I'm trying to left join a table where there are two identical fields

I want to select all records in a field from the left table and only
those records from the identical field of the right table which are not
bigger or smaller then 1

both field in the tables are integer data type

Can someone help

I tried this code but it does not work, I'm not sure if I can use these
operators <>
__________________________________________
SELECT Table1.field1,Table2.field1
FROM Table1 RIGHT JOIN Table2 ON (Table1.field1 = Table2.field2) AND
not (Table1.field1 (Table2.field2+1)) AND not (Table1.field1 <
(Table2.field2-1))
__________________________________________

[Stu]

Try this:

DECLARE @Table1 TABLE (Col1 int)
DECLARE @Table2 TABLE (Col1 int)

INSERT INTO @Table1
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 9
INSERT INTO @Table2
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
SELECT t1.col1, t2.col1
FROM @Table1 t1
LEFT JOIN @Table2 t2 ON t1.col1 BETWEEN t2.col1-1 AND t2.col1+1
It that ain't it, post DDL and sample data.

HTH,
Stu

eu*******@hotmail.com wrote:

I'm trying to left join a table where there are two identical fields

I want to select all records in a field from the left table and only
those records from the identical field of the right table which are not
bigger or smaller then 1

both field in the tables are integer data type

Can someone help

I tried this code but it does not work, I'm not sure if I can use these
operators <>
__________________________________________
SELECT Table1.field1,Table2.field1
FROM Table1 RIGHT JOIN Table2 ON (Table1.field1 = Table2.field2) AND
not (Table1.field1 (Table2.field2+1)) AND not (Table1.field1 <
(Table2.field2-1))
__________________________________________

[boa]

* eu*******@hotmail.com wrote, On 14.08.2006 19:42:

I'm trying to left join a table where there are two identical fields

I want to select all records in a field from the left table and only
those records from the identical field of the right table which are not
bigger or smaller then 1

both field in the tables are integer data type

Can someone help

I tried this code but it does not work, I'm not sure if I can use these
operators <>
__________________________________________
SELECT Table1.field1,Table2.field1
FROM Table1 RIGHT JOIN Table2 ON (Table1.field1 = Table2.field2) AND
not (Table1.field1 (Table2.field2+1)) AND not (Table1.field1 <
(Table2.field2-1))
__________________________________________

select t1.f1, t2.f1
from t1, t2
where t1.f1 between t2.f1 - 1 and t2.f1 + 1

Is that what you meant? :-)

HTH
Bjørn