Please help in building SQL query for this scenario

Transact-SQL
1

Hi,
Please help in getting all suppliers whose productid is prodid1 along with counts of these suppliers customers and orders count.

My SQL structure is below:

,..............
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'
......

2

Seems like a straight forward query. What have you tried? I'd rather not do your homework for you

3

I tried below query but my query giving wrong result and duplicate rows. Please help

select s.supplierid, s.suppliername,

(Select count(customerid) from customers where c.supplierid = s.supplierid) as customercount,

(Select count(orderid) from orders 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"

4

Now you are spamming with same question

5

Are you sure you even ran your query???

Your Customer table has field ordered, but your query is using orderid in join below

Inner join customers c on c.supplierid = s.supplierid
Inner join orders o on o.orderid = c.orderid

Suppliers has a field called ProdID, but your query is using ProductID in the where clause
Where s.productid = "prodid1"

and you have double quotes around ProdID1 in the where clause. At least try something.

6

Thankyou Mike & Yosiasz, Here is my updated query.

(Distinct is the update on supplierid)

select distinct s.supplierid, s.suppliername,

(Select count(distinct customerid) from customers where c.supplierid = s.supplierid and c.customername = 'your name') as customercount,

(Select count(distinct orderid) from orders where o.orderid = c.orderid and o.ordername = 'my order') 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.prodid = 'prodid1'
7

Your code still doesn't compile. The DDL you provided doesn't match your query. The issue is that someone else will find this and try to use it and it doesn't work. Here's one solution

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), orderid 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'

select s.supplierid, s.suppliername, 
	  count(distinct c.CustomerID) as CustomCount,
	  count(Distinct o.OrderID) as OrderCount,
	  count(o.OrderID) as TotalOrderCount
From #suppliers as s
	join #customers c 
		on c.supplierid = s.supplierid
	join #orders o 
		on o.orderid = c.orderid
where s.prodid = 'prodid1'
group by s.supplierid, s.suppliername