Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011
    Posts
    6

    Unanswered: Return a 0 value

    Hello Everyone (again)

    I have written the following query for MS ACCESS and I can't figure out why it won't produce a 0 for one of the product rows. Leaves the entire row out instead of marking the "NbrPlacedOrders" row with a 0.

    SELECT p.code, p.Description, COUNT(IIF(isnull(so.CID), 0, so.CID)) AS NbrPlacedOrders
    FROM Product AS p LEFT JOIN (SalesOrderProduct AS sop LEFT JOIN SalesOrder AS so ON sop.SOID = so.SOID) ON p.PID = sop.PID
    WHERE so.Status = 'Placed'
    GROUP BY p.Code, p.Description;

    Can anyone point me in the correct direction.

    Thanks (again) in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    three things

    first, you have a LEFT OUTER JOIN like this --
    Code:
    SalesOrderProduct AS sop LEFT JOIN SalesOrder AS so
    this is supposed to give you all the SalesOrderProduct rows, with or without matching SalesOrder rows

    but then you have this in the WHERE clause --
    Code:
     so.Status = 'Placed'
    this will effectively turn it into an inner join, because any row from the left table which had no matching row from the right table, i.e. where the columns from the right table are all set to NULL by the LEFT OUTER JOIN, those rows are filtered out, because NULL cannot be equal to anything, especially not 'Placed'

    so you might as well have written INNER JOIN in the first place

    secondly, under what conditions would you have a row in the SalesOrderProduct table for a sales order that doesn't exist?? that's right, none, because that would be a serious flaw in your application

    so it really should be an INNER JOIN

    finally, you have this --
    Code:
     COUNT(IIF(isnull(so.CID), 0, so.CID))
    that's going to count everything, if you think it through

    if so.CID is null, it will count a 0, which is not null, and if so.CID isn't null, it will count it, again a non-null-- taking both cases into consideration, it will count everything

    did these explanations help?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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