Solved

Query to find out records with duplicate of two columns combination

Posted on 2014-03-07
5
582 Views
Last Modified: 2014-03-08
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
Comment
Question by:heyday2004
5 Comments
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 250 total points
ID: 39914320
Try
SELECT c1, c2, FROM a INNER JOIN b ON a.c1=b.c1

Open in new window

HTH,
Dan
0
 
LVL 5

Assisted Solution

by:NARANTHIRAN
NARANTHIRAN earned 125 total points
ID: 39914321
select distinct TableA.*
  from TableA
       join TableB
         on TableA.A = TableB.A
        and TableA.B = TableB.B
0
 
LVL 54

Accepted Solution

by:
Julian Hansen earned 125 total points
ID: 39914390
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
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 250 total points
ID: 39914397
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
 

Author Closing Comment

by:heyday2004
ID: 39915174
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question