Solved

Compare two columns in different tables and extract difference between them

Posted on 2004-08-24
5
1,115 Views
Last Modified: 2012-06-21
I am using MS-SQL Query Analyser.

I have two tables ("A" & "B") that both have a column named "X".  Both columns contain the same type of information.  There are some differences betwen the contents of the 2 columns.

In particular, Table A col X contains a list of approx 100000 distinct numbers.  Table B col X contains approx 1 million numbers (not distinct).

I need to find out which numbers in table B col X do not exist in table A col X.  Help please!!!!!!  Thank you!
0
Comment
Question by:thedaveg155
  • 2
5 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11880496
This works in Oracle, I think you can change it if neccessary to  MS-SQL:

Select X from B
MINUS
Select X from A;
0
 

Accepted Solution

by:
thedaveg155 earned 0 total points
ID: 11881096
Just figured it myself.  Thanks for the comment but it didn't work for me.

Either:
SELECT DISTINCT * FROM A
WHERE X Not In (SELECT DISTINCT X FROM B)
Or:
SELECT A.* FROM A
LEFT JOIN B on A.X = B.X
WHERE B.X Is Null
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 11890224
You can ask 0-point question in a Community Support for refunding points :)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12032443
This question has been answered by the asker.

mlmcc
DB Reporting Tools PE
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question