SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Looking for help with a SQL Query

    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:

    Code:
    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?
    renkai.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Renkai View Post
    I have a table called Orders and another table called OrderDetails.
    and yet the query you posted references "vwOrders" which smells a lot like a view, and "vwOrderStatusTypes" which looks nothing like an OrderDetails table

    Quote Originally Posted by Renkai View Post
    How can I include in my query something that lets me know if there was a product of a certain productType in the order.
    where's this productType column? which table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah! Yes indeed these are views, not tables. The productType column is on the vwOrderDetails view.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Renkai View Post
    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.
    what about ost.Name? do you still need that? are there any column from vwOrderStatusTypes that you need besides productType?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Withut seeing the table schemas something like this would be my guess:

    Code SQL:
    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.
    The only code I hate more than my own is everyone else's.

  8. #8
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    can't you do this in your application language (php or whatever) while you're looping over the products for an order?
    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. #10
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    That would be correct, thought you were using MySQL.
    The only code I hate more than my own is everyone else's.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •