By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,081 Members | 861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,081 IT Pros & Developers. It's quick & easy.

finding mismatched rows between identical tables based on 2 or more cols

P: n/a
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)

Jun 8 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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, rs*********@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)
Jun 8 '07 #2

P: n/a
Here are two different approaches:

-- SQL Server 2005 only.
SELECT ColA, ColB FROM RS_A
EXCEPT
SELECT ColA, ColB FROM RS_B;

-- SQL Server 2000.
SELECT ColA, ColB FROM
( SELECT 'A' AS Source, ColA, ColB
FROM RS_A
UNION ALL
SELECT 'B' AS Source, ColA, ColB
FROM RS_B ) AS T
GROUP BY ColA, ColB
HAVING COUNT(*) = 1 AND MIN(Source) = 'A'

HTH,

Plamen Ratchev
http://www.SQLStudio.com
Jun 10 '07 #3

P: n/a
Roy & Plamen :

Thank for you for you valuable inputs.

- RS
Jun 11 '07 #4

P: n/a
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

Hi Harvey :

I was implementing this solution at another place and i mentioned an
AND instead of a WHERE. and it did not work.
So i used WHERE and viola, things worked.
But actually how can i use a WHERE Clause?
I thought WHERE Clauses were for the FROM table and not for the tables
that are joined to the FROM table

Is my assumption correct ? Obviously it is wrong,
So is it a normal thing to mention conditions where only one table is
needed(like in the above example)
in the WHERE Clause, as then i can do that will all my other queries
where i am joining, and i always have mentioned unique conditions
pertaining to that table alone, using an AND clause.

thank you in advance
RS

Jun 22 '07 #5

P: n/a
The entire idea of using LEFT OUTER JOIN with a NULL test to give the
same results as NOT EXISTS is based on the fact that the WHERE clause
executes after the JOIN processing.

JOINs occurs before anything else. The WHERE clause acts on the
result of the JOIN. If you changed the WHERE to an AND in the example
shown, the test B.ColA IS NULL would move into the ON clause and
become part of the JOIN. Inside the JOIN that column will never be
NULL.

Personally I much prefer the NOT EXISTS syntax over this approach. I
think it shows what you are trying to retrieve - rows in one table
without matches in the other table - more clearly.

Roy Harvey
Beacon Falls, CT

On Fri, 22 Jun 2007 04:48:02 -0700, rs*********@gmail.com wrote:
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

Hi Harvey :

I was implementing this solution at another place and i mentioned an
AND instead of a WHERE. and it did not work.
So i used WHERE and viola, things worked.
But actually how can i use a WHERE Clause?
I thought WHERE Clauses were for the FROM table and not for the tables
that are joined to the FROM table

Is my assumption correct ? Obviously it is wrong,
So is it a normal thing to mention conditions where only one table is
needed(like in the above example)
in the WHERE Clause, as then i can do that will all my other queries
where i am joining, and i always have mentioned unique conditions
pertaining to that table alone, using an AND clause.

thank you in advance
RS

Jun 22 '07 #6

P: n/a
Thank you for your explanation.
Now, i understand how the WHERE and AND works and can use it much more
effectively in my statements.

RS

Jun 22 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.