CODEWITHSUNDEEP
sqlsql-servert-sql
Jeremy F.
Jeremy F.

Reputation: 1878

Comparison between two tables

I am trying to pull in an ID number and name from one table that checks to see if the same ID number does not exist in another table. I am getting the error:

Subquery returned more than 1 value.

Below is my query.

Select Table1.ID,
       Table1.Name
  From Table1
 Where Table1.ID not in ((select (Table2.ID)
                            from Table2 ) 
                         COLLATE SQL_Latin1_General_CP1_CS_AS)

Upvotes: 1

Views: 1061

Answers (5)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

The best performing query for a non-existence test is to ... use NOT-exists. The worktable does not need to collect all rows of the LEFT JOIN just to PRUNE later using the WHERE clause. The first existence match aborts the table1 row.

Select Table1.ID,
       Table1.Name
  From Table1
 Where NOT EXISTS (SELECT *
                   from Table2
                   WHERE Table2.ID = TABLE1.ID)

Upvotes: 0

John K.
John K.

Reputation: 5474

This part...

((select (Table2.ID) from Table2) COLLATE SQL_Latin1_General_CP1_CS_AS)

seems to be collecting the list of rows... Try removing the COLLATE SQL_Latin1_General_CP1_CS_AS

and just use:

(select (Table2.ID) from Table2)  )

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453898

The parentheses and collate are either unneeded or in the wrong place but I can't get the same error as you are getting.

with Table1(ID, Name) aS
(
SELECT 'A1',2 union all
select 'B1',2
),
Table2(ID, Name) aS
(
SELECT 'A1',2 union all
select 'D1',2
)

Select Table1.ID,
       Table1.Name
  From Table1
 Where Table1.ID not in (select Table2.ID   COLLATE SQL_Latin1_General_CP1_CS_AS
                            from Table2 )

works fine. You should favour NOT EXISTS over NOT IN on nullable columns but I assume the ID field is unlikely to be NULLABLE?

Upvotes: 1

Rowland Shaw
Rowland Shaw

Reputation: 38128

Would it not be a lot easier to do:

SELECT Table1.ID,
       Table1.Name
FROM   Table1
LEFT JOIN Table2 ON Table1.ID = Table2.ID
WHERE  Table2.ID is null

Upvotes: 6

Adam Robinson
Adam Robinson

Reputation: 185703

The COLLATE statement only applies to an expression, not to a set. Are you certain that you actually need to specify a collation here? If so, you need to move it inside the subselect, so that it reads this way:

select Table2.ID COLLATE SQL_Latin1_General_CP1_CS_AS from Table2

Upvotes: 0

Related Questions