Solved

Compare two columns in different tables and extract difference between them

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

806 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