I have 4 tables as below.
Products (prodid, prodname)
Suppliers (supplierid, suppliername, prodid)
customers (customerid, customername, orderid, supplierid)
orders (orderid, ordername)
I want to retrieve Supplier records whose prodid is = "prod1" and count (customers) whose name = "sample customer" and count(orders) whose ordername = "some order" for that supplier.
Output I want is:
All suppliers who's prodid is prodid1 and count of customers n count of orders.
I tried below query:
select s.supplierid, s.suppliername,
(Select count(customerid) where c.supplierid = s.supplierid) as customercount,
(Select count(orderid) where o.orderid = c.orderid) as ordercount
From suppliers as s
Inner join customers c on c.supplierid = s.supplierid
Inner join orders o on o.orderid = c.orderid
Where s.productid = "prodid1"
But my query giving wrong result and duplicate rows.
Please help in correcting this or suggestions new?