Transact SQL WHERE CURSOR

Question

Possible Duplicate:
SQL - find records from one table which don't exist in another

i have this table

TableA  
uID   |   uData  
1     |   111  
2     |   222  
3     |   333  
4     |   444  
5     |   555  

Table B  
uID   |   uData2  
1     |   222  
2     |   444

what i want is to get only the data from TableA which not in the data from TableB

OUTPUT:

uID   |   uData  
1     |   111  
3     |   333 
5     |   555  

so far, i made this solution

DECLARE allData CURSOR FOR
SELECT uData
FROM         TableA;
OPEN allData;
FETCH NEXT FROM allData;

print @allData;

--WHILE @@allData = '222'
--  BEGIN
--      FETCH NEXT FROM allData;
--  END
CLOSE allData;
DEALLOCATE allData;
GO

and got this error

Must declare the scalar variable "@allData".

is there any other way to do this without using while in sql or any other solution? :)

Answer 1

Try this

    SELECT *
    FROM TableA 
    WHERE uData NOT IN (
       SELECT uData2
       FROM TableB
    )

Answer 2

Just use a left join and filter out the records where there is data from the second table:

select t1.uID, t1.uData
from TableA t1
left join TableB t2 on t2.uData2 = t1.uData
where t2.uData2 is null

Answer 3

No need for a cursor; just exclue values from TableB using the following method:

select tableA.* 
from   tableA 
where  tableA.uData not in (select TableB.uData2 from TableB)

Further, your syntax for the CURSOR is wrong. Check out this quick example for proper CURSOR syntax for future reference.

Answer 4

Use this query:

SELECT * FROM tableA 
LEFT JOIN tableB ON tableA.Uid = tableB.Uid
WHERE tableB.Uid IS NULL