Query to find out records with duplicate of two columns combination

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
heyday2004Asked:
Who is Participating?
 
Julian HansenConnect With a Mentor Commented:
A Cross Join also works

SELECT * FROM tableA, tableB
WHERE tableA.c1 = tableB.c1 AND tableA.c2 = tableB.c2

Open in new window

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
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
Try
SELECT c1, c2, FROM a INNER JOIN b ON a.c1=b.c1

Open in new window

HTH,
Dan
0
 
Naranthiran DConnect With a Mentor Commented:
select distinct TableA.*
  from TableA
       join TableB
         on TableA.A = TableB.A
        and TableA.B = TableB.B
0
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
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

Open in new window

0
 
heyday2004Author Commented:
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.