SQL Statement to select duplicate records appearing more than 2 times

Question

I need help to get the solution for this condition. I have a table containing records, there is a field sku, in this record i have sku's appearing multiple times. Table structure is like this rid|id|sku|name

rid is auto_increment, where is id is varchar, if any sku is available on table multiple times the record looks like this

rid  id  sku     name
---  --  ------  --------------
1    3   rs-123  test product
2    3   rs-123  test product
3    4   rs-125  test product 2
4    4   rs-125  test product 2
5    4   rs-125  test product 2
6    6   rs-126  test product 3

I used this sql statement to get records that appears only once

SELECT *
FROM test
GROUP BY id
HAVING ( COUNT(id) = 1 )

This brings the records that are only added once, so according to above give record only rid 6 is the output

I tried to modify the above code to this to get the result of the records which are added 2 times

    SELECT * FROM test 
GROUP BY id 
HAVING ( COUNT(id) = 2 )

The result I am getting is of those record which are added 2 times, but the issue is the output is appearing only 1 record like this;

rid  id  sku     name
---  --  ------  ------------
1    3   rs-123  test product

I need to fetch all rows of record that are added 2 times in the database. Please help

Answer 1

SELECT t.rid
     , t.id
     , t.sku
     , t.name
  FROM test t
  JOIN ( SELECT s.sku 
           FROM test s 
          GROUP BY s.sku
         HAVING COUNT(1) > 1
       ) d
    ON d.sku = t.sku

The inline view aliased as d returns the sku values that appear more than once in the table. We can join the results of that query to the table to get all rows that have a sku that matches.

Are id and sku interchangeable? That wasn't clear to me. (If id is dependent on sku and sku is dependent on id, then you can replace references to sku with references to id in that query.