Re: need help with LEFT JOIN statemen

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

.

Relevant Pages

  • Re: Not In and Not Exists
    ... > SQL Server will try to optimize query plans so neither technique ought to ... Col1 int NULL ... > INSERT INTO Table1 VALUES ... > INSERT INTO Table2 VALUES ...
    (microsoft.public.sqlserver.programming)
  • Re: insert rows : generating sequence numbers
    ... For the sake of simplicity and clarity, ... create table Table1 (Col1 int NOT NULL unique) ... insert into Table2 values ...
    (microsoft.public.sqlserver.programming)
  • Re: simple stored procedure
    ... CREATE TABLE table1 (col1 int) ... INSERT INTO table1 VALUES ... CREATE TABLE table2 ...
    (microsoft.public.inetserver.asp.db)
  • return records based on max value joined on another table
    ... INSERT INTO @TABLE1 ... DECLARE @TABLE2 TABLENOT NULL,ZONE INT NOT NULL) ... State Zone MAXCHARGE ...
    (microsoft.public.sqlserver.programming)
  • Re: Tricky Visual Basic Code help...
    ... moves the current record into a new record in table2. ... this with a little VBA code and a couple custom queries. ... Set a "pushFlag" column in the record in table1 to a specific ... If a duplicate is not found, ...
    (microsoft.public.access.formscoding)