?
Solved

Query to find out records with duplicate of two columns combination

Posted on 2014-03-07
5
Medium Priority
?
588 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 1000 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 500 total points
ID: 39914321
select distinct TableA.*
  from TableA
       join TableB
         on TableA.A = TableB.A
        and TableA.B = TableB.B
0
 
LVL 58

Accepted Solution

by:
Julian Hansen earned 500 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 35

Assisted Solution

by:Dan Craciun
Dan Craciun earned 1000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 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