ranjan satpathy ranjan satpathy - 4 months ago 9
SQL Question

MySql :- Count duplicate records including foreign key relation

I have two tables 1.Purchase Order Table and another is 2.Supplier Table.
In between them foreign relation is there(Supplier_id present in Purchase Order Table).

In Purchase order the supplier_id may be duplicate so my requirement is I need Query to count the duplicate supplier_id as well as fetch the supplier name from the supplier table based upon the supplier_id.
Tables

Query

Answer

Simply join the supplier table to your result:

select supplier_id,count(*) c, supplier_name 
from purchase_order join supplier s on s.id=supplier_id 
group by supplier_id , supplier_name
having c>1