CODEWITHSUNDEEP
sql-serverjoincase
user3066571
user3066571

Reputation: 1471

SQL show all rows even if no matching criteria

I'm messing around with the WideWorldImporters Database Microsoft gives out for people learning SQL (trying to get better at it as I often do SQL at work). I'm trying to get the sum of all sales made by state in 2013.

I got that together fine, but the problem is, there are several states that don't have any listed, and don't show up in the list. How do I make them show up and just have a 0 for their sales? From what I've found, this has to do with JOINs, but I've messed around with the various types, and it doesn't seem to do the trick. As you can see, I also tried a CASE which also hasn't helped. Any tips would be greatly appreciated!

SELECT 
    StateProvinceName,
    CASE WHEN SUM(Quantity * UnitPrice) IS NULL 
        THEN '0' ELSE SUM(Quantity * UnitPrice) * (AVG(TaxRate) / 100 + 1) END AS Sales
FROM 
    Application.StateProvinces

    FULL JOIN Application.Cities
        ON Application.StateProvinces.StateProvinceID = Application.Cities.StateProvinceID
    FULL JOIN Sales.Customers
        ON Application.Cities.CityID = Sales.Customers.DeliveryCityID
    FULL JOIN Sales.Orders
        ON Sales.Customers.CustomerID = Sales.Orders.CustomerID
    FULL JOIN Sales.OrderLines
        ON Sales.Orders.OrderID = Sales.OrderLines.OrderID

WHERE OrderDate >= '2013-01-01'
AND OrderDate < '2014-01-01'

GROUP BY StateProvinceName
ORDER BY Sales DESC

As you can see, the sales amounts are many tables removed from the state names, I don't know if that complicates the issue.

Upvotes: 1

Views: 444

Answers (2)

user6691848
user6691848

Reputation:

You also may be looking for a LEFT JOIN in this situation. A Full Join will give you all of the records from tables on both sides of your join, if there is a match in both tables or not. This would give you cities, orders, customers and order lines that do not have matching record in Application.StateProvinces.

By using a FULL JOIN instead of a LEFT JOIN, you will return null values in your StateProvinceName column.

Upvotes: 0

James Z
James Z

Reputation: 12317

When you add something to where part, it will cause that table to be inner joined to the rest of the query, and I'd guess that's your issue here. Assuming the dates are in Orders table, does this work better:

FROM  Application.StateProvinces
FULL JOIN Application.Cities
    ON Application.StateProvinces.StateProvinceID = Application.Cities.StateProvinceID
FULL JOIN Sales.Customers
    ON Application.Cities.CityID = Sales.Customers.DeliveryCityID
FULL JOIN Sales.Orders
    ON Sales.Customers.CustomerID = Sales.Orders.CustomerID
    and OrderDate >= '2013-01-01' AND OrderDate < '2014-01-01'
FULL JOIN Sales.OrderLines
    ON Sales.Orders.OrderID = Sales.OrderLines.OrderID

You should also use YYYYMMDD date format instead, others can be misinterpreted, see this question.

Upvotes: 1

Related Questions