Results 1 to 6 of 6

Thread: Inner Join

  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Unanswered: Inner Join

    Hi

    I have two tables, one containing stock item information (STOCK) - stock code is unique. and sales orders (SORDERS) - each item can be sold many times.
    I am trying to write a stock re-order report, looking at current levels, min / max and then a total of current sales for each code.

    The following works ok for this.
    strSql = "SELECT STOCK.CODE, STOCK.DESCR, STOCK.SUPPLIER, STOCK.STK, SUM(SORDERS.QTY), STOCK.ORDERED, STOCK.MINSTOCK, STOCK.MAXSTOCK FROM STOCK INNER JOIN SORDERS ON STOCK.CODE = SORDERS.CODE GROUP BY STOCK.CODE ORDER BY STOCK.SUPPLIER, STOCK.CODE"

    What I then want to do is start to add some logic to the report to show specific records (probably something like show record if (STOCK.STK + STOCK.ORDERED) < SUM(SORDERS.QTY).

    This is where I have the problem, as I don't seem to be able to use the SUM(SORDERS.QTY) after the INNER JOIN. So, the following doesn't work (I know that there is a sode which has a total of 10)

    strSql = "SELECT STOCK.CODE, STOCK.DESCR, STOCK.SUPPLIER, STOCK.STK, SUM(SORDERS.QTY), STOCK.ORDERED, STOCK.MINSTOCK, STOCK.MAXSTOCK FROM STOCK INNER JOIN SORDERS ON STOCK.CODE = SORDERS.CODE WHERE SUM(SORDERS.QTY) = 10 GROUP BY STOCK.CODE ORDER BY STOCK.SUPPLIER, STOCK.CODE"

    I'm sure there's a good reason for it not working, but I can't quite figure out what I'm doing wrong.

    For reference, I'm using Excel to query a FoxPro database in the same directory as the excel file:
    cnn.Open "DRIVER=Driver para o Microsoft Visual FoxPro; Deleted = Yes; Null=Yes; Collate = Machine; " & _
    "BackgroundFetch = Yes; Exclusive = No; SourceType = DBF; " & _
    "SourceDB = " & Excel.Workbooks(1).Path

    Thanks in advance for any help,
    Richard.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    richardfry, the reason our query does work has to do with the order of certain processes. The Where clause is processed first and operates on a single row. The Group By is processed much later and it summarizes 1 to many rows down to 1 row per unique value(s). So at the time the Where clause is processing, the value of SUM(SORDERS.QTY) has not yet been determined.

    I don't know anything about running queries from Excel so I don't know if either of these suggestions will work (or if they are available in your database version).

    Basically you need to get the result of the query with the Group by first and then run the query to compare the Summed values. This can be accomplished with either a Common Table Expression or a Nested/Derived table.

    Common Table Expression example:
    Code:
    WITH CTE_RESULT AS
    (SELECT STOCK.CODE
          , STOCK.DESCR
          , STOCK.SUPPLIER
          , STOCK.STK
          , SUM(SORDERS.QTY) AS SUM_QTY
          , STOCK.ORDERED
          , STOCK.MINSTOCK
          , STOCK.MAXSTOCK
    FROM STOCK 
           INNER JOIN 
         SORDERS
           ON STOCK.CODE = SORDERS.CODE 
    GROUP BY STOCK.CODE 
    )
    SELECT column-list
    FROM CTE_RESULT
    WHERE STK + ORDERED < SUM_QTY
    Nested/Derived table example:
    Code:
    SELECT column-list
    FROM (SELECT STOCK.CODE
               , STOCK.DESCR
               , STOCK.SUPPLIER
               , STOCK.STK
               , SUM(SORDERS.QTY) AS SUM_QTY
               , STOCK.ORDERED
               , STOCK.MINSTOCK
               , STOCK.MAXSTOCK
          FROM STOCK 
                 INNER JOIN 
               SORDERS
                 ON STOCK.CODE = SORDERS.CODE 
          GROUP BY STOCK.CODE 
         ) AS A
    WHERE STK + ORDERED < SUM_QTY

  3. #3
    Join Date
    Jan 2010
    Posts
    3
    Hi Stealth,
    Many thanks for your quick reply.
    It looks like VFP doesn't support CTE, but nested tables should be ok.
    I will try it this afternoon and hopefully get it working.
    Thanks again
    Richard.

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92

    Having

    The HAVING clause restricts selection of groups in the same way that a WHERE clause restricts selection of rows.

    SELECT ...
    FROM ...
    WHERE ...
    GROUP BY ...
    HAVING SUM(SORDERS.QTY) >= 10

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    good point, JarlH. I must not have been fully awake yet. HAVING is a easier solution.

  6. #6
    Join Date
    Jan 2010
    Posts
    3
    Hi,
    Thanks for the replies. I've got the HAVING clause working - I just need to sort my logic out now!
    Also working through the nested tables as I think this will be a useful exercise for the future.
    Many thanks again,
    Richard.

Posting Permissions

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