Solved

Compare two columns in different tables and extract difference between them

Posted on 2004-08-24
5
1,112 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now