Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Find Records That exist but not with specified criteria

    I apologize on the title so let me explain better. I have a stores table and a dues table. I want my query to show the payments a specified store has made in a specified dues year. If there are no records for the specified dues year I'd like the store number and 0 for the due amount returned.

    The stores table has a primary key, StoreNum, which relates to a foreign key, StoreNum, in the dues table where each record also contains a DueAmount and DuesYear. Here was my first attempt:

    SELECT tblStores.StoreNum, tblDues.DueAmount
    FROM tblStores LEFT JOIN tblDues
    ON tblStores.StoreNum=tblDues.StoreNum
    WHERE tblDues.DuesYear=2006 AND tblStores.StoreNum=10000

    (note: changing the DuesYear to 2005 will return records)

    I figured the LEFT JOIN below would return the StoreNum and a DueAmount of NULL if there weren't any records for the DuesYear, but nothing is returned at all.

    I have a solution but it involves temp tables and update queries. I'm wondering if there is a simple one query solution that I'm overlooking or even a way to do it without temp tables.

    Thanks in advance,
    GG

  2. #2
    Join Date
    Jan 2004
    Posts
    145
    I found a way with subqueries.

    SELECT tblStores.StoreNum, IIf([Paid] Is Null,0,[Paid]) AS PaidTotal
    FROM tblStores LEFT JOIN

    (SELECT tblStores.StoreNum, tblDues.DuesAmount AS Paid
    FROM tblStores INNER JOIN tblDues ON tblStores.StoreNum = tblDues.StoreNum
    WHERE (tblStores.StoreNum = 10000 AND tblDues.DuesYear = 2006)) AS Poo

    ON tblStores.StoreNum = Poo.IDQ_NO
    WHERE (tblStores.StoreNum = 10000);

    The result is either the dues paid in the specified DuesYear or 0.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just include the "WHERE" condition on year in the join condition ("ON") and you'll see at least one line per tblStores row.

    If you actually want to see exactly one row per tblStores row, add a group by:
    Code:
    SELECT tblStores.StoreNum, SUM(tblDues.DueAmount)
    FROM   tblStores LEFT OUTER JOIN tblDues
           ON tblStores.StoreNum=tblDues.StoreNum
          AND tblDues.DuesYear=2005
    GROUP BY tblStores.StoreNum
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2004
    Posts
    145
    This thread has been dead awhile but I found an improvement. I tried Peter's suggestion, but I'm using Access and it doesn't support a where clause in the join. Below is what I came up with. I'm not sure the performance implications it might cause on larger tables, but it works fine on 30K records.

    SELECT
    tblStores.StoreNum,
    Sum(IIf([tblDues.DuesYear]="2006",[tblDues.DuesAmount],0)) AS Paid

    FROM
    tblStores LEFT JOIN tblDues ON tblStores.StoreNum = tblDues.StoreNum

    GROUP BY
    tblStores.StoreNum
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I didn't mean that the word "WHERE" should appear in the join condition: just add the condition (without WHERE, but with AND) to the join condition in the ON.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jan 2004
    Posts
    145
    I knew my wording was going to be confusing. I used the sql you posted and it didn't work for the reason mentioned. The AND tblDues.DuesYear=2005 gives an error in Access. I didn't literally put the "WHERE" in the join. I just meant the preceding code is a "WHERE" condition.

    If you can get your sql to work in Access let me know because I think it would work better than my solution in certain situations.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  7. #7
    Join Date
    Jan 2004
    Posts
    145
    I reviewed the error given and it says "Join Expression Not Supported". It appears you can't use a scalar when comparing in the "ON" part. You have to use a field from one of the tables. I was able to get it working with the syntax below though. Basically it works like a parameter. The tblStores.Whatever doesn't actually exist so Access prompts you for a value and the query runs. If you use an actual parameter you get the error above.

    Works:
    SELECT tblStores.StoreNum, SUM(tblDues.DueAmount)
    FROM tblStores LEFT OUTER JOIN tblDues
    ON tblStores.StoreNum=tblDues.StoreNum
    AND tblDues.DuesYear=tblStores.Whatever
    GROUP BY tblStores.StoreNum

    Doesn't Work:
    SELECT tblStores.StoreNum, SUM(tblDues.DueAmount)
    FROM tblStores LEFT OUTER JOIN tblDues
    ON tblStores.StoreNum=tblDues.StoreNum
    AND tblDues.DuesYear=[Please enter a dues year]
    GROUP BY tblStores.StoreNum

    I didn't thoroughly test this, but it appears to work fine.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by gwgeller
    I reviewed the error given and it says "Join Expression Not Supported". It appears you can't use a scalar when comparing in the "ON" part.
    SQL doesn't impose such a restriction. So it must be from your specific product that does not adhere to the SQL standard in this respect.

    How about this:
    Code:
    SELECT tblStores.StoreNum, SUM(tblDues.DueAmount)
    FROM   tblStores LEFT OUTER JOIN tblDues ON
              ( tblStores.StoreNum=tblDues.StoreNum )
    WHERE  tblDues.DuesYear = [Please enter a dues year] OR
           tblDues.DuesYear IS NULL
    GROUP BY tblStores.StoreNum
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gwgeller
    Doesn't Work:
    SELECT tblStores.StoreNum, SUM(tblDues.DueAmount)
    FROM tblStores LEFT OUTER JOIN tblDues
    ON tblStores.StoreNum=tblDues.StoreNum
    AND tblDues.DuesYear=[Please enter a dues year]
    GROUP BY tblStores.StoreNum
    Might work:
    Code:
    SELECT tblStores.StoreNum, SUM(tblDues.DueAmount)
    FROM   tblStores LEFT OUTER JOIN tblDues
           ON (
              tblStores.StoreNum=tblDues.StoreNum
          AND tblDues.DuesYear = [Please enter a dues year]
              )
    GROUP BY tblStores.StoreNum
    Will probably work:
    Code:
    SELECT tblStores.StoreNum, D.Sum_DueAmount
    FROM   tblStores LEFT OUTER JOIN 
    ( select StoreNum
           , SUM(DueAmount) as Sum_DueAmount
        from tblDues
       where DuesYear = [Please enter a dues year]
      GROUP BY StoreNum 
    ) as D
      ON D.StoreNum = tblStores.StoreNum
    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
  •