Solved

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

Posted on 2014-03-07
578 Views
Query to find out records with duplicate of two columns combination

I have two tables that have the same structure but different data. 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
2   UK
3   USA

Again, note: I need to compare combined fields of c1 + c2

Thanks for any samples
0
Question by:heyday2004

LVL 34

Assisted Solution

Dan Craciun earned 250 total points
Try
``````SELECT c1, c2, FROM a INNER JOIN b ON a.c1=b.c1
``````
HTH,
Dan
0

LVL 5

Assisted Solution

NARANTHIRAN earned 125 total points
select distinct TableA.*
from TableA
join TableB
on TableA.A = TableB.A
and TableA.B = TableB.B
0

LVL 51

Accepted Solution

Julian Hansen earned 125 total points
A Cross Join also works

``````SELECT * FROM tableA, tableB
WHERE tableA.c1 = tableB.c1 AND tableA.c2 = tableB.c2
``````
When joining on more than 1 field I prefer to use a cross join than an inner join - it doesn't make much of a difference - just a preference.
0

LVL 34

Assisted Solution

Dan Craciun earned 250 total points
Sorry, it should of been:
``````SELECT a.c1, a.c2,
FROM a INNER JOIN b
ON a.c1=b.c1 AND a.c2 = b.c2
``````
0

Author Closing Comment

0

## Featured Post

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

#### Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!