CODEWITHSUNDEEP
sql
user1288456
user1288456

Reputation: 1

SQL Query Adding All Customers even if they don't have any sales

Below is my relatively simple sales query but I want it to pick up all of my dealers, not just the dealers that have sales. Is this possible?

SELECT dbo.tblDealer.DealerID, Firstname, LastName,
SUM(dbo.tblOrderDetail.PSVAmount) AS "PSV", 
SUM(dbo.tblOrderDetail.UplineVolume) AS "Upline",
SUM(dbo.tblOrderDetail.LineTotal) AS "Product Total"
FROM tblOrderDetail 
inner join tblOrder 
on dbo.tblOrderDetail.OrderID = dbo.tblOrder.OrderID 
inner join tblDealership on dbo.tblOrder.DealershipID = dbo.tblDealership.DealershipID 
inner join tblDealer on dbo.tblDealership.DealerID = dbo.tblDealer.DealerID
where orderDate between '3/01/12' and '3/21/12 23:59:59'
and dbo.tbldealer.active = -1

GROUP BY dbo.tblDealer.DealerID, Firstname, LastName

Upvotes: 0

Views: 195

Answers (1)

Lamak
Lamak

Reputation: 70668

You should change the order of your joins, and do an outer join instead of an inner join:

SELECT dbo.tblDealer.DealerID, Firstname, LastName,
SUM(dbo.tblOrderDetail.PSVAmount) AS "PSV", 
SUM(dbo.tblOrderDetail.UplineVolume) AS "Upline",
SUM(dbo.tblOrderDetail.LineTotal) AS "Product Total"
FROM tblDealer
inner join tblDealership
on dbo.tblDealership.DealerID = dbo.tblDealer.DealerID
left join tblOrder 
on dbo.tblOrderDetail.OrderID = dbo.tblOrder.OrderID AND orderDate between '3/01/12' and '3/21/12 23:59:59'
left join tblOrder 
on dbo.tblOrderDetail.OrderID = dbo.tblOrder.OrderID 
where dbo.tbldealer.active = -1
GROUP BY dbo.tblDealer.DealerID, Firstname, LastName

Upvotes: 3

Related Questions