Re: need help with LEFT JOIN statemen

Giganews Newsgroups
Subject: Re: need help with LEFT JOIN statemen
Posted by:  Stu (stuart.ainswor…@gmail.com)
Date: 14 Aug 2006

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

eurolin…@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))
> __________________________________________

Replies

None

In response to

need help with LEFT JOIN statemen posted by eurolin…@hotmail.com on 14 Aug 2006