Just found this issue today while building a new development machine. If you uninstall (or upgrade, apparently) Office 2007 to Office 2010 it can cause an issue with Design View in Visual Studio 2008. Ran into this issue today and thought it might come in handy, especially since many will soon be upgrading to Office 2010.
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.
