question

okc123 avatar image
okc123 asked

please help in sql query with count & joins

Hi,

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.

sqljoinscount
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

What did you try already?

sounds like you know exactly what you want, but you're not showing us your code to see where your syntax is failing you

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

okc123 avatar image
okc123 answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

okc123 avatar image
okc123 answered

@JonCrawford, could you please help?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.