Posted by Roy Harvey on 06/08/07 17:01

Here are two approaches. I personally prefer the EXISTS version.

SELECT A.ColA, A.ColB
FROM RS_A A
WHERE NOT EXISTS
(select * from RS_B B
where A.ColA = B.ColA
and B.ColB = B.ColB)

SELECT A.ColA, A.ColB
FROM RS_A A
LEFT OUTER JOIN RS_B B
ON A.ColA = B.ColA
AND B.ColB = B.ColB
WHERE B.ColA IS NULL

Roy Harvey
Beacon Falls, CT

On Fri, 08 Jun 2007 06:36:17 -0700, rshivaraman@gmail.com wrote:

>CREATE TABLE [RS_A] (
> [ColA] [varchar] (10)
> [ColB] [int] NULL
>)
>
>CREATE TABLE [RS_B] (
> [ColA] [varchar] (10)
> [ColB] [int] NULL
>)
>
>INSERT INTO RS_A
>VALUES ('hemingway' , 1)
>INSERT INTO RS_A
>VALUES ('vidal' , 2)
>INSERT INTO RS_A
>VALUES ('dickens' , 3)
>INSERT INTO RS_A
>VALUES ('rushdie' , 4)
>
>INSERT INTO RS_B
>VALUES ('hemingway' , 1)
>INSERT INTO RS_B
>VALUES ('vidal' , 2)
>
>I need to find all the rows in A which do not exist in B
>by matching on both ColA and ColB
>
>so the output should be
>dickens 3
>rushdie 4
>
>So if i write a query like this , I dont get the right result set
>
>SELECT A.ColA, A.ColB
>FROM RS_A A
>INNER JOIN RS_B B
>ON A.ColA <> B.ColA
>OR B.ColB <> B.ColB
>
>
>
>But if i do the following, i do get the right result, but following
>seems convoluted.
>
>SELECT A.ColA, A.ColB
>FROM RS_A A
>WHERE ColA + CAST(ColB AS VARCHAR)
>NOT IN (SELECT ColA+CAST(ColB AS VARCHAR) FROM RS_B B)

• Next in forum: WHAT HAPPENED?
• Prev in forum: Re: SQLServer Table Partitioning
• Thread view: Re: finding mismatched rows between identical tables based on 2 or more cols

[Reply to this message]