CODEWITHSUNDEEP
sqlselect
sh4rkyy
sh4rkyy

Reputation: 402

Find items with duplicated values

I would like to find orders, where product name is used more than once. In this example the result should be order_id 10

Tables:

Orders 
order_id
9
10
11

Order_details 
order_id, product_id  
9,        7    
10,       5
10,       6
11,       6
11,       7

Products 
product_id, product_name, price
5,          potato,       4.99
6,          potato,       7.5
7,          orange,       7.99

Upvotes: 0

Views: 33

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can use this one below

with Order_details(order_id, product_id) as
(
 select  9,7    union all
 select 10,5    union all
 select 10,6    union all
 select 11,6    union all
 select 11,7
),
 Products(product_id, product_name, price) as
(
 select 5, 'potato', 4.99 union all
 select 6, 'potato', 7.5  union all
 select 7, 'orange', 7.99
)    
select o.order_id, p.product_name
  from order_details o
  join products p on o.product_id = p.product_id
 group by o.order_id, p.product_name
having count(p.product_name) > 1;

order_id  product_name
--------  ------------
   10       potato

P.S. above with..as structure depends on your DBMS product, might be removed only that part if doesn't work

Rextester Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

If you just want the the orders then you can compare the number of lines with valid product names and the number of different product names. If all product names are unique, then the counts are the same. If they are different, then there is a duplicate somewhere:

select ol.order_id
from order_lines ol join
     products p
     on ol.product_id = p.product_id
group by ol.order_id
having count(p.product_name) > count(distinct p.product_name)

Upvotes: 2

Related Questions