Looking for help with a SQL Query

Databases
1

I have a table called Orders and another table called OrderDetails.

My Order table contains information about the order (who placed it, OrderDate, their BilltoID, etc)

The OrderDetails table contains line by line information about what products were part of the order. Example, productID, ProductName, productType, etc.

I have to begin with a query giving a list of the orders based on their BilltoID. That query is:

SELECT o.ID,OrderDate,ShipDate,CALC_GrandTotal,ShipTrackingNum,o.ShipType, 
CASE ost.Name WHEN 'Taken' THEN 'In Progress'
ELSE ost.Name END AS OrderStatus
FROM vwOrders o INNER JOIN 
vwOrderStatusTypes ost ON o.OrderStatusID=ost.ID
WHERE BillToID= 123456

How can I include in my query something that lets me know if there was a product of a certain productType in the order. I don’t have to know the productID or any information about it, other than if the order contained a certain productType within the order. Something like a Boolean value called ‘HasChart’ which is set to 1 or 0. Any suggestions?

2

and yet the query you posted references “vwOrders” which smells a lot like a view, and “vwOrderStatusTypes” which looks nothing like an OrderDetails table

where’s this productType column? which table?

3

Ah! Yes indeed these are views, not tables. The productType column is on the vwOrderDetails view.

4

what about ost.Name? do you still need that? are there any column from vwOrderStatusTypes that you need besides productType?

5

Thanks, I do still need to hang on to ost.Name. No other columns on vwOrderStatusTypes that I care about besides productType. I just need to know if the order contains a certain type of product, yes or no.

6

well, doing anything like this (if the order contains a certain type of product) can only be done at the order (“one”) level, not at the product (“many”) level, but you do want to return product data…

… so to get this done in the query will take what to my mind is unnecessary extra processing (a correlated subquery, which is quite inefficient)

can’t you do this in your application language (php or whatever) while you’re looping over the products for an order?

7

Withut seeing the table schemas something like this would be my guess:


SELECT 
     o.ID
     ,OrderDate
     ,ShipDate
     ,CALC_GrandTotal
     ,ShipTrackingNum
     ,o.ShipType
     ,CASE ost.Name WHEN 'Taken' THEN 'In Progress' ELSE ost.Name END AS OrderStatus
     ,MAX(CASE WHEN od.productType IN ('typename') THEN 1 ELSE 0 END) hasProductType
FROM 
    vwOrders o 
INNER 
 JOIN 
    vwOrderStatusTypes ost 
   ON 
    o.OrderStatusID=ost.ID
INNER
 JOIN
    vwOrderDetails od
   ON
    o.ID = od.orderID #change this name of foreign key column#
WHERE 
    BillToID= 123456
GROUP
   BY
    o.ID

Also in terms of readability you should consider qualifying each column with the table alias.

8

this works! I was thinking a case statement but had such trouble coming up with something that worked.
The only issue I came across with this was the Group By, instead of

GROUP BY o.ID

With just o.ID, I get an error about each item “not contained in either an aggregate function or the GROUP BY clause”. So I had to add all the selected values:

GROUP BY o.ID, o.OrderDate, o.ShipDate,o.CALC_GrandTotal,o.ShipTrackingNum,o.ShipType,ost.Name

Does this make sense? Or would there be a better way to handle it?

Thanks again.

9

If it was php I could. But I’m working with an asp.net datagrid and I don’t know how to loop through and stick in the values into the same datagrid. Actually I’m not sure that’s possible at all in .net.

10

That would be correct, thought you were using MySQL.