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

Question

The solution to this topic is evading me.

I have a table similar (apart from other fields that have nothing to do with my question):

NAME, CARDNUMBER, MemberType

Now I want the view to show lines in which the card number and item type are identical. Both of these fields are integers. The name is VARCHAR. The name is not unique, and duplicate the card number, the type of participant must show the same name.

those. if the following table:

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

I would like to:

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

it can simply be sorted by card number to make it useful to people.

What is the most effective way to do this?

Answer 1

Since you mentioned names that can be duplicated, and that the duplicate name still means another person and should appear in the result set, we need to use GROUP BY HAVING COUNT (*)> 1 to really detect the cheats. Then attach this to the main table to get a complete list of results.

Also, since it seems from your comments that you are wrapping this in a view, you need to separate 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 script example

Answer 2

You can use exists for this:

 select * from yourtable y where exists ( select 1 from yourtable y2 where y.name <> y2.name and y.cardnumber = y2.cardnumber and y.membertype = y2.membertype) 

• SQL Fiddle Demo

Answer 3

What is the most effective way to do this?

I believe 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 

Request Plan: http://www.sqlfiddle.com/#!2/0abe3/1