• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

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
0
heyday2004
Asked:
heyday2004
4 Solutions
 
Dan CraciunIT ConsultantCommented:
Try
SELECT c1, c2, FROM a INNER JOIN b ON a.c1=b.c1

Open in new window

HTH,
Dan
0
 
NARANTHIRANCommented:
select distinct TableA.*
  from TableA
       join TableB
         on TableA.A = TableB.A
        and TableA.B = TableB.B
0
 
Julian HansenCommented:
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 CraciunIT 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

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now