Solved

Query to find out records with duplicate of two columns combination

Posted on 2014-03-07
5
580 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 52

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now