I have a table where we have barcodes and order numbers, so, I am trying to find all the barcodes that are being assigned to multiple orders.

Example table

expected output

CodePudding user response：

What you should do is to find barcodes you need (b_mult) and then join them to themselves in order to find order numbers

select *
  from barcodes b
  join (select barcode
                    from barcodes
                   group by barcode
                  having count(1) > 1) b_mult
     on b.barcode = b_mult.barcode  

an example

CodePudding user response：

Use a window function:

with find_multi as (
  select barcode, ord_no, 
         count(*) over (partition by barcode) as item_cnt
    from order_item
)
select barcode, ord_no
  from find_multi
 where item_cnt > 1;

CodePudding user response：

We count(*) to find duplicates.

select    barcode
         ,ord_no
from     (
          select *
                 ,count(*) over(partition by barcode) as cnt
          from   t
         ) t
where    cnt > 1
order by barcode, ord_no

Fiddle