Posted by rshivaraman on 06/08/07 13:36

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: MI5 Persecution: Goldfish and Piranha 29/9/95 (5087)
• Prev in forum: MI5 Persecution: Watch Out, Forger About 27/9/95 (3573)
• Thread view: finding mismatched rows between identical tables based on 2 or more cols

[Reply to this message]