Exclusive Query

I want a query that will give me all rows that are in one table but not in
another.
For example:
Table1.ID Table2.ID Result.ID
1 1 4
2 2 5
3 3 6
4
5
6
Thanks
Try:
select id from table1 a
where not exists
(select * from table2 b
where a.id = b.id)
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||That seems to work well. Now I'd like to add the result back into the second
table so that table2 ends up like table1.
I tried:
INSERT INTO table2 ( id )
SELECT table1.id FROM table1 WHERE NOT EXISTS
( SELECT table2.id FROM table2 WHERE table1.id = table2.id )
but that doesn't seem to work.
"Vishal Parkar" wrote:

> Try:
> select id from table1 a
> where not exists
> (select * from table2 b
> where a.id = b.id)
> --
> Vishal Parkar
> vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
>
>
|||i dont know why it shouldn't work. are you getting any error?
see following example.
create table table1(id int)
create table table2(id int)
insert into table1 values(1)
insert into table1 values(2)
insert into table1 values(3)
insert into table2 values(1)
insert into table2 values(2)
INSERT INTO table2 ( id )
SELECT table1.id FROM table1 WHERE NOT EXISTS
( SELECT table2.id FROM table2 WHERE table1.id = table2.id )
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

exclude rows

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:

table 1
----
id
----
a
b
c
d
e
f
g

table 2
----
id / id2
----
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4

need results
----
id
----
a
e
g

any suggestions?

thanksSELECT * FROM tblOne WHERE id_field IN (SELECT id_field FROM tblTwo WHERE other_id = 1)

EXCEPT not working

TIA. Here is my situation: I have two tables that I need to find the perform an EXCEPT op on.

Table1: ToBeAddedCodes
CodeID - varchar(14)

Table2: ExistingCodes
ExistingCodeID - varchar(14)
DateIssued - datetime
Active - bit
...&c

I perform the following command, to no avail:

select CodeID
from ToBeAddedCodes
intersect
select ExistingCodeID
from ExistingCodes

Specifically, the following error appears:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'intersect'.

I don't understand what the issue is... Please help. Thanks.

The syntax should be valid if you are on SQL Server 2005, any version prior 2005 won′t support the Intersect keyword. I think thats your problem. Seems that you are connected to a SQL Server 2000 instance.

HTH, Jens SUessmeyer.

-
http://www.sqlserver2005.de
-

|||Or the database is running in 8.0 compatibility level.|||The new set operators will work in 80 and other compatibility modes also. So that is not the problem. User is either running on a older version of SQL Server or old CTP releases of SQL Server 2005. The set operators EXCEPT/INTERSECT was added late in the development cycle only.|||Please post the version of SQL Server (@.@.version) that you are running this code against.|||

Thanks for the replies ppl. Here's the requested information:

Information obtained from Help/About (about indicates "MS SQL Server 2005":

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Information obtained from "select @.@.version":

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

|||

>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation

>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The server version is 8.* which means it is SQL Server 2000. You are running SQL Server Management Studio though which ships with SQL Server 2005. The client doesn't have anything to do with the server language features. So you need to create your tables on a SQL Server 2005 server and try the EXCEPT query.

|||Did you happen to install SQL Server 2005 on a machine with an existing SQL Server 2000 installed? You might have mistaken the installation to be an upgrade (just like what I did a couple of months back )|||Well, I have no control over the infrastructure or anything in fact... I am just stepping in with the .NET development. Unfortunately, it does seem as if they are a bit out of date. Yes, on my client machine I am using 2005 but the database is in 2000.