Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL - select rows that have the same value in two columns

Tags:

sql

join

mysql

The solution to the topic is evading me.

I have a table looking like (beyond other fields that have nothing to do with my question):

NAME,CARDNUMBER,MEMBERTYPE

Now, I want a view that shows rows where the cardnumber AND membertype is identical. Both of these fields are integers. Name is VARCHAR. Name is not unique, and duplicate cardnumber, membertype should show for the same name, as well.

I.e. if the following was the table:

JOHN       | 324   | 2
PETER      | 642   | 1
MARK       | 324   | 2
DIANNA     | 753   | 2
SPIDERMAN  | 642   | 1
JAMIE FOXX | 235   | 6

I would want:

JOHN       | 324   | 2
MARK       | 324   | 2
PETER      | 642   | 1
SPIDERMAN  | 642   | 1

this could just be sorted by cardnumber to make it useful to humans.

What's the most efficient way of doing this?

like image 549
nickdnk Avatar asked May 25 '14 22:05

nickdnk


People also ask

How do I check if two columns have the same value in mysql?

Find duplicate values in multiple columns In this case, you can use the following query: SELECT col1, COUNT(col1), col2, COUNT(col2), ... FROM table_name GROUP BY col1, col2, ... HAVING (COUNT(col1) > 1) AND (COUNT(col2) > 1) AND ...

Can we use two columns in where clause in SQL?

When we have to select multiple columns along with some condition, we put a WHERE clause and write our condition inside that clause. It is not mandatory to choose the WHERE clause there can be multiple options to put conditions depending on the query asked but most conditions are satisfied with the WHERE clause.

How do I select the same row in SQL?

To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.


2 Answers

Since you mentioned names can be duplicated, and that a duplicate name still means is a different person and should show up in the result set, we need to use a GROUP BY HAVING COUNT(*) > 1 in order to truly detect dupes. Then join this back to the main table to get your full result list.

Also since from your comments, it sounds like you are wrapping this into a view, you'll need to separate out the subquery.

CREATE VIEW DUP_CARDS
AS
SELECT CARDNUMBER, MEMBERTYPE
FROM mytable t2
GROUP BY CARDNUMBER, MEMBERTYPE
HAVING COUNT(*) > 1

CREATE VIEW DUP_ROWS
AS
SELECT t1.*
FROM mytable AS t1
INNER JOIN DUP_CARDS AS DUP
ON (T1.CARDNUMBER = DUP.CARDNUMBER AND T1.MEMBERTYPE = DUP.MEMBERTYPE )

SQL Fiddle Example

like image 151
woot Avatar answered Sep 23 '22 02:09

woot


What's the most efficient way of doing this?

I believe a JOIN will be more efficient than EXISTS

SELECT t1.* FROM myTable t1
JOIN (
    SELECT cardnumber, membertype
    FROM myTable
    GROUP BY cardnumber, membertype
    HAVING COUNT(*) > 1
) t2 ON t1.cardnumber = t2.cardnumber AND t1.membertype = t2.membertype

Query plan: http://www.sqlfiddle.com/#!2/0abe3/1

like image 38
FuzzyTree Avatar answered Sep 23 '22 02:09

FuzzyTree