I need help in fixing my SQL query.
I have 4 tables as below.
Products (prodid, prodname)
Suppliers (supplierid, suppliername, prodid)
customers (customerid, customername, orderid)
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.
Please help me in this question.
Answer by okc123 ·
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 results and duplicate rows.