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, supplierid, 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 resolution.
If you could provide sample data for each of the four tables, it will help us come up with the join quicker for you. 
You're missing a column(s) from the tables (or a whole other table).
We can link Products(P) and Suppliers(S). And separately we can link Customers(C) and Orders(O). But there's no way to link from either C/O table to the P/S tables.
Hi Sure.
Products table: have 2 rows
ProdId1, product1
ProdId2, product2
Suppliers table:
Supplier1, "some supplier", ProdId1
Supplier2, " other", ProdId1
Customers table:
CustomerId1, "cust1", orderid1, Supplier1
CustomerId2, "cust1", orderid2, Supplier2
CustomerId3, "cust1", orderid1, Supplier1
Orders table:
orderid1, " first order"
orderid2, "second order"
orderid3, "first order"
Now:
I will give input to my procedure as ProdId1 then , I need output as
SupplierId1 "some supplier" 2
SupplierId2 "other" 1
Suppliers who's prodid is "prodid1" and count of customers for this supplier whose customer name is "cust1" and count of orders for this supplier whose order name is " first order"
Please help me in building it.
Thanks much
Correct Scott. I missed in my post. Customer table has supplierid column. Please find above reply with some dummy data.
Need query for:
Suppliers who's prodid is "prodid1" and count of customers for this supplier whose customer name is "cust1" and count of orders for this supplier whose order name is " first order"
please help us help you. provide your data as proper DDL and DML
create table #Products(prodid varchar(50), prodname varchar(50)) --or whatever it is in your db
insert into #Products
select 'ProdId1', 'product1' union
select 'ProdId2', 'product2'
and do this for all other tables. Folks are busy and might not have time to do this.
Please find the statements.
create table Products(prodid varchar(50), prodname varchar(50))
insert into Products
select 'ProdId1', 'product1' union
select 'ProdId2', 'product2'
create table Suppliers(SupplierId varchar(50), suppliername varchar(50), prodid varchar(50))
insert into Suppliers
select 'SupplierId1', 'first supplier', 'ProdId1' union
select 'SupplierId2', 'some supplier', 'ProdId1'
create table Customers(SupplierId varchar(50), customerid varchar(50), customername varchar(50), ordered varchar(50))
insert into Customers
select 'SupplierId1', 'customerid1, 'my name', 'order1' union
select 'SupplierId2', 'customerid2', 'my name'
, 'order2' union
select 'SupplierId1', 'customerid3', 'my name'
, 'order1'
create table orders(orderid varchar(50), ordername varchar(50))
insert into Products
select 'order1', 'my order' union
select 'order2, 'your order'
Now I want to fetch the suppliers whose product id is prodid1 along with count of customers whose name is 'my name' and orders count with order name as 'my order'
SELECT C.supplierid, S.suppliername, COUNT(*) AS order_count
FROM Customers C
INNER JOIN Suppliers S ON S.supplierid = C.supplierid AND S.prodid = 'prodid1'
INNER JOIN Orders O ON O.orderid = C.orderid AND O.ordername = 'first order'
WHERE C.customername = 'cust1'
GROUP BY C.supplierid, S.suppliername
ORDER BY C.supplieridHi Scott, can we fetch customer count too?
Oops, I need to adjust the query:
SELECT C.supplierid, S.suppliername,
COUNT(DISTINCT C.orderid) AS order_count,
COUNT(DISTINCT C.supplierid) AS supplier_count
FROM Customers C
INNER JOIN Suppliers S ON S.supplierid = C.supplierid AND S.prodid = 'prodid1'
INNER JOIN Orders O ON O.orderid = C.orderid AND O.ordername = 'first order'
WHERE C.customername = 'cust1'
GROUP BY C.supplierid, S.suppliername
ORDER BY C.supplieridHi Scott,
unfortunately its not giving me correct result. Please let me know if any thing is missing 
Just my data with tables & data below:
Suppliers whose prodid is prod1 and count of customers for these supplierids whose name = 'my name' and whose order = 'my order'
As per my data, I need to get output with two rows as
SupplierId1, 2, 1
SupplierId2, 1, 0
create table Products(prodid varchar(50), prodname varchar(50))
insert into Products
select 'ProdId1', 'product1' union
select 'ProdId2', 'product2'
create table Suppliers(SupplierId varchar(50), suppliername varchar(50), prodid varchar(50))
insert into Suppliers
select 'SupplierId1', 'first supplier', 'ProdId1' union
select 'SupplierId2', 'some supplier', 'ProdId1'
create table Customers(SupplierId varchar(50), customerid varchar(50), customername varchar(50), ordered varchar(50))
insert into Customers
select 'SupplierId1', 'customerid1', 'my name', 'order1' union
select 'SupplierId2', 'customerid2', 'my name'
, 'order2' union
select 'SupplierId1', 'customerid3', 'my name'
, 'order1'
create table orders(orderid varchar(50), ordername varchar(50))
insert into orders
select 'order1', 'my order' union
select 'order2', 'your order'
You should not get Supplier2 cause they are not associated with order = 'my order'
The data you provided and the design of it needs some rework? Is this real world scenario or are you learning SQL , or is this some sort of homework?
Are you interested in a better table design?
But supplier2 is having prodid1.
I want to have all suppliers which are associated with given product id along with counts.
Basically I'm expecting two rows here suppliers which have Prodid1 and count of customer and orders based on condition.
if count is empty with given customer name or orders it should be as 0 , 0
but supplier2 is not associated with my order. so either your data is wrong or your requirements are?
Can't we get, all the suppliers which are associated with product id as prodid1 along with count of customer and count of orders based on condition? Is there any way ?
I'm fine if these suppliers not matching with the conditions for customer name or order names (in this case need their counts as zeros)
thankyou,
That would require separate queries.
You need to very clearly the specific requirements you have for the results you want.
Hi Scott,
yes please if it needed multiple queries im fine. need to get that result. Sorry if my question was not clear.
I'm using below query but it's giving me duplicate results. Can some one help in correcting this please..
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.