Select query with same value in row-CodePudding

I have a table structure like this:

order_item_id order_id product_id
1                513     120
2                213     121
3                513     120
4                312     131
5                312     131
6                102     123

I want to have a SQL query where I can get the following results:

order_item_id order_id product_id
1                513     120
3                513     120
4                312     131
5                312     131

I used the following SQL query to fetch the results, but it doesn't help:

SELECT * 
FROM `stg_83087_wc_order_product_lookup`
WHERE `order_id` = `order_id` and `product_id` = `product_id`

The only question I have to is to get the next value of the row so I can make the comparison here.

CodePudding user response：

You basically need to check the existance of similar rows:

select *
from t
where exists (
  select * from t t2
    where t2.order_id = t.order_id
      and t2.product_id = t.product_id
      and t2.order_item_id != t.order_item_id
);