[SOLVED] get no value when I run the query to this question

get no value when I run the query to this question

The assignment question is...

List the order's customer name, order status, date ordered, count of items on the order, and average quantity ordered where the count of items on the order is greater than 300.

I'm using Adventure Works 2019 for the assignment.

The answer I have been able to come up with is....

SELECT
    LastName + ', ' + FirstName AS 'Customer Name',
    ssoh.Status AS 'Order Status',
    ssoh.OrderDate AS 'Date Order',
    SUM(ssod.Orderqty) AS 'Count of Items',
    AVG(ssod.Orderqty) AS 'Average Quantity'
FROM
    Person.Person pp
    JOIN Sales.SalesOrderHeader ssoh ON pp.BusinessEntityID = ssoh.CustomerID
    JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid
GROUP BY
    LastName + ', ' + FirstName,
    ssoh.OrderDate,
    ssoh.Status
HAVING
    SUM(ssod.OrderQty) > 300

When I change the inner join to an outer join I get null values for the customer name. I doubled checked the "customerID" foreign key to make sure it the same as the primary key "BusinessEntityID" and get results. Any ideas would be greatly appreciated. Thanks

Solution

Based on having a quick look at the schema online (I don't have it installed), it's my understanding that you need to join Person.Person through Sales.Customer to Sales.SalesOrderHeader. So perhaps try the following:

FROM
    Person.Person pp
    JOIN Sales.Customer sc ON pp.BusinessEntityID= sc.PersonID
    JOIN Sales.SalesOrderHeader ssoh ON sc.CustomerID= ssoh.CustomerID
    JOIN Sales.SalesOrderDetail ssod on ssoh.SalesOrderID = ssod.SalesOrderid