Solved

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

Posted on 2014-03-08
214 Views
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
0
Question by:heyday2004

LVL 34

Accepted Solution

Dan Craciun earned 200 total points
``````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 )
``````

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

HTH,
Dan
0

Author Comment

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

LVL 4

Assisted Solution

rshq earned 100 total points
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)) )
``````
0

LVL 11

Assisted Solution

John_Vidmar earned 100 total points
``````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
``````
0

LVL 31

Assisted Solution

awking00 earned 100 total points
select c1, c2 from a
where not exists
(select 1 from b
where a.c1 = b.c1
and a.c2 = b.c2);
0

LVL 34

Assisted Solution

Dan Craciun earned 200 total points
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

Author Closing Comment

Sorry for the late reply. Thanks for all the help.
0

## Featured Post

### Suggested Solutions

Mysqli Query 5 40
Query question 4 29
SQL Query Syntax Error 9 29
How to incorporate a join with current sql query syntax 2 15
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Creating and Managing Databases with phpMyAdmin in cPanel.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…