------------------------------------------------------------------------
-- John David Ayers ----------------------------------------------------
-- October 15, 2009 ----------------------------------------------------
-- JOIN COMMAND 2 ------------------------------------------------------
------------------------------------------------------------------------

SELECT ProductName, OrderNumber
FROM Products as p LEFT OUTER JOIN Order_Details as od ON p.ProductNumber = od.ProductNumber
WHERE OrderNumber IS NULL;

SELECT	ProductName, CONVERT(CHAR(12), OrderDate, 107) As [Order Date]
FROM (Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber)
RIGHT OUTER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber;

-- SAME AS ABOVE
SELECT ProductName,
CONVERT(CHAR(12), OrderDate, 107)
As [Order Date]
FROM Products as p LEFT OUTER JOIN
(
	SELECT Orders.OrderDate, Order_Details.ProductNumber
	FROM Orders INNER JOIN Order_Details ON Orders.OrderNumber = Order_Details.OrderNumber
) AS nq
ON p.ProductNumber = nq.ProductNumber;

SELECT ProductName
FROM Products
WHERE QuantityOnHand =
(
	SELECT MAX(QuantityOnHand)
	FROM Products
);

SELECT CustLastName, CustFirstName
FROM Customers
WHERE CustomerID NOT IN
(
	SELECT DISTINCT Customers.CustomerID
	FROM ((Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
	INNER JOIN Order_Details on Orders.OrderNumber = Order_Details.OrderNumber)
	INNER JOIN Products ON Products.ProductNumber = Order_Details.ProductNumber
	WHERE ProductName LIKE '%helmet%'
);
