How to select rows where a combination of 2 columns is the same

Question

The title seems to do a really poor job at describing my problem; let me try and explain it with a simplified example:

lets say I have the following table:

_______________________
|id|variant_id|attr_id|
|__|__________|_______|
|1 |15        |110    |
|2 |15        |110    |
|3 |20        |152    |
|4 |20        |110    |
|5 |21        |110    |
|__|__________|_______|

Now, what I'd like to have is a query that selects all rows where a combination of the variant_id and attr_id columns occurs more than once. Basically in this example it should select row 1 and 2, because their combination of variant_id and attr_id occurs more than once in the table.

Is that possible? My head hurts from trying to think of a possible solution.

Meherzad · Accepted Answer · 2013-04-29 07:38:03Z

6

Try this query

SELECT a.* FROM 
tbl a 
inner join 
tbl b
ON a.variant_id =b.variant_id AND a.attr_id = b.attr_id
WHERE a.id <> b.id;

Hope this helps

answered Apr 29, 2013 at 7:38

Meherzad

8,5611 gold badge33 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xNidhogg Over a year ago

AH of cause! I forgot you could join tables on themselves! Thanks this work flawlessly.

realnumber3012 · Accepted Answer · 2013-04-29 07:37:23Z

5

SELECT variant_id, attr_id
FROM YouTable
GROUP BY variant_id, attr_id
HAVING COUNT(*) > 1

answered Apr 29, 2013 at 7:37

realnumber3012

1,0626 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to select rows where a combination of 2 columns is the same

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related