Query to find out records with non-duplicate of two columns combination

This is an extension for question:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28383519.html#a39914320

I have two tables that have the same structure but data might be slightly different. Table names are a and b and column names to be used are c1, c2 (there are many other columns for table a or b)

Example:
select c1, c2 from a
c1 c2
2  UK
3  USA
5  Japan

select c1, c2 from b:
c1  c2
2   UK
3   USA
5   France

After the query, result should be (retrieve c1, c2 info from table a)
c1 c2
5 Japan

Again, please note: I need to compare combined fields of c1 + c2. It's assumed both table a and b have the same number of records and mostly identical
heyday2004Asked:
Who is Participating?
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
SELECT a.c1, a.c2 FROM a WHERE a.c1 NOT IN (
SELECT a.c1 
    FROM a INNER JOIN b 
        ON a.c1=b.c1 AND a.c2 = b.c2 )

Open in new window


Basically select records that were not selected in the previous question.

HTH,
Dan
0
 
heyday2004Author Commented:
Thanks, but seems not exactly working as requested. I needs to compare combo of c1 and c2, seems above query didn't list all the occurrences.
0
 
rshqConnect With a Mentor Commented:
Hi
  I think we have some blank in data in c1,c2 fields.
  please use LTRIM,RTRIM function in  "Dan Craciun" query
SELECT a.c1, a.c2 FROM a WHERE Ltrim(Rtrim(a.c1)) NOT IN (
SELECT Ltrim(Rtrim(a.c1)) 
    FROM a INNER JOIN b 
        ON Ltrim(Rtrim(a.c1))=Ltrim(Rtrim(b.c1)) AND Ltrim(Rtrim(a.c2)) = Ltrim(Rtrim(b.c2)) )

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
John_VidmarConnect With a Mentor Commented:
SELECT	a.c1
,	a.c2
FROM	a
LEFT
JOIN	b	ON	a.c1 = b.c1
		AND	a.c2 = b.c2
WHERE	b.c1 IS NULL

Open in new window

0
 
awking00Connect With a Mentor Commented:
select c1, c2 from a
where not exists
(select 1 from b
 where a.c1 = b.c1
 and a.c2 = b.c2);
0
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
Can you please paste/attach samples of the data that was not listed?

We all gave queries that work perfectly with your sample from OP (tested and double tested). We need to see actual data if we're to correct the queries.

Dan
0
 
heyday2004Author Commented:
Sorry for the late reply. Thanks for all the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.