Tables
Customer(CustomerID, CustomerName)
Order(OrderID, CustomerID, OrderAmt, ShipTo)
Select
L.CustomerID,
L.CustomerName,
Count(R1.ShipTo) OrdersShippedToTexas,
Count(R2.ShipTo) OrdersShippedToColorado
From
Customer L
Left Outer Join Order R1 ON (L.CustomerID = R1.CustomerID AND R1.ShipTo = 'TX')
Left Outer Join Order R2 ON (L.CustomerID = R2.CustomerID AND R2.ShipTo = 'CO')
Group By
L.CustomerID,
L.CustomerName
Can I do a multiple LOJ on the same table?
I want output like this:
CustomerID Customer OrdersShippedToTexas OrdersShippedToColorado
1 Susie 5 8
2 Doug 7 4
I have been using views where I have used table alias R1 and R2. Views work - Tables don't. Why?
I want a simple count report where I am using the join condition to identify the wanted related records.
Start Free Trial
