Then fip/flop the table names! Hehe.


-----Original Message-----
From: Vanessa van Gelder [mailto:[Email address protected]
Sent: Friday, August 31, 2007 8:45 AM
To: Edwards, Ed
Cc: 'LazyDBA Discussion'
Subject: RE: comparing two tables

Ah no I want to se whats in the other and what is not in the one.. ?\
Caphiche

Vanessa Fredanus- van Gelder| SQL DBA | DXS Systems | Tel. +27 21 853
3317|
Fax. +27 21 854 7953

You must be the change you want to see in the world. Mahatma Gandhi





-----Original Message-----
From: Edwards Ed [mailto:Ed.[Email address protected]
Sent: 31 August 2007 02:33
To: [Email address protected]
Subject: RE: comparing two tables

**A LazyDBA.com subscriber has responded to your lazydba.com post**
**LazyDBA.com mail shield has forwarded you this email,
**and removed any attachments, and kept your email address secret
**from this person, and any viruses/trojans.
**If you reply to this email, the person will see your email address as
normal
**Anything below this line is the original email text


So, here's an example, assuming we are comparing tables A and B, and the
primary key of both tables is ID:

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID









Please take a few minutes to provide feedback on the quality of service
you
received from our staff. The Department of Education values your
feedback as
a customer. Commissioner of Education Jeanine Blomberg is committed to
continuously assessing and improving the level and quality of services
provided to you.Simply use the link below. Thank you in advance for
completing the survey.


[Email address protected]




-----Original Message-----
From: Vanessa van Gelder
[Email address protected]
Sent: Friday, August 31, 2007 8:24 AM
To: LazyDBA Discussion
Subject: comparing two tables

select * from srv.masson.dbo.coding a left join masson.dbo.coding b

on a.articlekey = b.articlekey

and a.systemkey = b.systemkey

where b.articlekey is null



this should bring back everything that's isn't present in table b
shouldn't
it?



argh



Vanessa Fredanus- van Gelder| SQL DBA | DXS Systems | Tel. +27 21 853
3317|
Fax. +27 21 854 7953

When I was young, I used to admire intelligent people; as I grow older,
I
admire kind people. Abraham Joshua Heschel





---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---

DXS - The Essential Healthcare Information Solution
http://www.dxs-systems.com/



DISCLAIMER:

This communication (including any attachments) may contain confidential
or proprietary information. If you are not the intended recipient and
you have received this communication in error, you should destroy it
without copying, disclosing or otherwise using its contents and notify
the sender immediately of the error.

Internet communications are not necessarily secure, and may be
monitored, intercepted or changed after they are sent. DXS and its
subsidiaries do not accept liability for any loss you may suffer as a
result of interception, or any liability for such changes.

MS Sql Server LazyDBA home page