help with an sql statement

[sthmpsn1]

I have two tables we will call them table1 and table2 that have a column named loginID. I need to create a select statement that grabs all the loginID from table1 that are not in table2. Is there a compare statement in sql or something like that to do this???

Scott

 

[sunila7]

Hi,

Try this...

SELECT loginID from Table1
Where not EXISTS (SELECT LoginID From Table2)


Sunil

 

[Zathras]

"...two tables we will call them table1 and table2 that have a column named loginID. I need to create a select statement that grabs all the loginID from table1 that are not in table2..."

SELECT A.LOGINID FROM Table1 A
LEFT OUTER JOIN Table2 B ON A.LOGINID=B.LOGINID
WHERE B.XXXXX IS NULL

XXXXX is the name of a column in Table2 that is known always to have a non-null value.

Here is a complete script for testing:

create table #Table1 (LoginID char(8))
insert into #Table1 values('JONES')
insert into #Table1 values('SMITH')
insert into #Table1 values('BROWN')
insert into #Table1 values('GREEN')
create table #Table2 (LoginID char(8),XXXXX smallint)
insert into #Table2 values('JONES',1)
insert into #Table2 values('SMITH',2)
insert into #Table2 values('GREEN',4)
SELECT A.LOGINID FROM #Table1 A
LEFT OUTER JOIN #Table2 B ON A.LOGINID=B.LOGINID
WHERE B.XXXXX IS NULL
drop table #Table1
drop table #Table2

 

[anilcognos]

select Loginid from table1 where Loginid not in
(select Loginid from table2)

 

[Zathras]

For small tables, any of the above should be ok. But for large tables, subqueries can be slow. For example to find out which of 50,000 customers haven't placed an order in the past three years, a subquery in the orders table could take a while.

Bottom line: use whichever tool is appropriate for the size of the job.

 

[sthmpsn1]

Thanks everyone!!