Posted by Stu on 10/02/97 11:55

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



eurolinux@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))
> __________________________________________

• Next in forum: copy database via tapefile no SQL 2005
• Prev in forum: need help with LEFT JOIN statemen
• Thread view: Re: need help with LEFT JOIN statemen

[Reply to this message]