Yesterday I spent the most time working on some complicated SQL queries. I learned how to use table aliases to reference the same table twice. My query was returning multiple records for a customer because some customers had multiple addresses. I wanted just the last address based on the maximum Address record ID number.
So I needed to select the Address ID from one table alias that matched the Maximum Address ID from the second table alias. The query is shown below:
SELECT Customers.uid, Customers.FirstName, Customers.LastName, Addr.Address1, Addr.Address2, Addr.City, Addr.State, Addr.Zip, Addr.Country, Addr.Phone, Customers.Email, CustomerPriceGroups.GroupName, Customers.Member_Expiration, MAX(Orders.DateOrdered) AS DateOrdered
FROM Customers, CustomerPriceGroups, Orders, OrderItems, Addresses Addr, Addresses Addr2
WHERE CustomerPriceGroups.uid = Customers.CustGroupID
AND Addr.CustomerID = Customers.uid
AND Addr2.CustomerID = Customers.uid
AND OrderItems.Category LIKE '%Partner%'
AND OrderItems.OrderID = Orders.uid
AND Orders.CustomerID = Customers.uid
GROUP BY Customers.uid, Customers.FirstName, Customers.LastName, Addr.uid, Addr.Address1, Addr.Address2, Addr.City, Addr.State, Addr.Zip, Addr.Country, Addr.Phone, Customers.Email, CustomerPriceGroups.GroupName, Customers.Member_Expiration
HAVING Addr.uid = MAX(Addr2.uid)
ORDER BY Customers.uid
Notes:
- use two table aliases to reference the same table twice
- apply any WHERE conditions to both table aliases
- select fields from one table alias
- include the ID field in the GROUP BY clause
- HAVING is very similar to WHERE except the statements within it are of an aggregate nature.
- ORDER BY must be after HAVING clause
