Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1

    Question Unanswered: Query help with receipts

    Hey all I am in need of some help with my query. I am testing this out with 2 users. However, it only seems to show the first user and never the other one. Here is my query string.
    Code:
    SELECT productr.id, staybridgedb.productr.itemPrice, productr.itemName,
    
    SUM(productr.itemsSold) AS ITEMSSOLD,
    SUM(productr.itemPriceTotal) AS PRICETOTAL,
    SUM(productr.transactTotal) AS TRANSTOTAL,
    SUM(productr.itemTax) AS TAX,
    SUM(productr.transactTotalTax) AS TOTALWITHTAX,
    
    productr.empID, staybridgedb.productr.transTime,
    productr.itemRUDate, staybridgedb.empid.UniqueID,
    empid.FName, staybridgedb.empid.LName
    FROM productr, empid
    WHERE productr.empid = empid.UniqueID
    AND itemRUDate >= '2009/09/03'
    GROUP BY itemName DESC
    It displays fine for the first user but never shows anything for the second user. And i've also checked to make sure the "UniqueID" is different for both of them and that it has the same dates "2009/09/03" when looking through the receipts.

    Any help would be great!

    David

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your problem is due to improper grouping

    you did not describe your tables, or how/why the employee is related to the product

    therefore it's not possible to guess whether you wanted product totals for each employee or employee totals for each product
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    Provided Answers: 1
    Ive solved it by just using the ID in both cases to insure it would go through.
    Code:
    SELECT itemname, itemsSold, empid, itemRUDate, itemPrice, itemPriceTotal, recpType, itemTransID,
    transTime, staybridgedb.empid.FName, staybridgedb.empid.LName
    From staybridgedb.productr, staybridgedb.empid
    WHERE productr.empid = '" & theUID & "'
    AND staybridgedb.empid.UniqueID = '" & theUID & "'
    AND itemRUDate = '2009/09/03'
    ORDER BY itemTransID, itemName ASC
    But thanks for the pointer!

    David

Posting Permissions

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