[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

two column PK and IN

erikv

3/22/2007 4:17:00 PM

Two simple tables with a two column PK:

CREATE TABLE [dbo].[auxData](
[id] [int] NOT NULL,
[group] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[auxData] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_auxData] PRIMARY KEY CLUSTERED
(
[id] ASC,
[group] ASC
))


CREATE TABLE [dbo].[test](
[id] [int] NOT NULL,
[group] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Label] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED
(
[id] ASC,
[group] ASC
))


I'd like to query TEST and return all id,group pairs that aren't in
AUXDATA.

How can I do that?

This is, I'm sure, very very wrong:
SELECT id,group
from test
where (ID NOT IN (select id from auxData) AND group NOT IN (select
group from auxData))

Thanks!
2 Answers

Aaron [SQL Server MVP]

3/22/2007 4:41:00 PM

0

> SELECT id,group
> from test
> where (ID NOT IN (select id from auxData) AND group NOT IN (select
> group from auxData))

SELECT id, group FROM test t
WHERE NOT EXISTS
(
SELECT 1 FROM auxData
WHERE id = t.id AND group = t.group
);

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...

erikv

3/22/2007 5:15:00 PM

0

Thank you very much!

>
> SELECT id, group FROM test t
> WHERE NOT EXISTS
> (
> SELECT 1 FROM auxData
> WHERE id = t.id AND group = t.group
> );
>
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfa...