Counting the Many
Recently in an interview I was asked to provide a query for the following tables:
ORDER
orderID
orderDate
ORDERITEM
orderItemID
orderID
OrderDescription
These tables have a one-to-many relationship, as in each order can have multiple order items. The objective of the SQL statement was to return all the orders with a count of how many order items each order had. My first solution involved using a join:
SELECT Orders.OrderID, Orders.OrderDate, COUNT(OrderItems.OrderItemID) AS NoOfItems FROM Orders LEFT JOIN OrderItems on Orders.OrderID = OrderItems.OrderID GROUP BY Orders.OrderID, Orders.OrderDate
This achieved the objective but it was pointed out that I could have just used a subquery. Using a subquery is simpler as well as avoids some of the possible issues using JOINs:
SELECT Orders.OrderId, Orders.OrderDate, (SELECT COUNT(*) FROM OrderItems WHERE Orders.OrderID = OrderItems.OrderID) AS NoOfItems FROM Orders
Ta-da! Two ways to get a count of items from the ‘many’ table in a one-to-many relationship. Pretty simple stuff but posted here for your convenience.
A Fool and His (or Her) Money…
Aaron’s Home Audio Tips for the night:
You get absolutely no advantage using an HDMI cable like this:
http://www.gspr.com/wireworld/ps_hdmi.html
Expensive optical cables also have no advantage. At the minimum all you need is a cable that doesn’t fall apart.
Theoretically, higher quality speaker cables *can* improve sound, however the improvements are most likely outside the realm of human hearing. There have been comparison tests with using coat hanger wire vs. using a high-quality speaker wire and listeners were unable to tell the difference.
My recommendation for any of your cable needs:
http://www.monoprice.com
