SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Threaded View

  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2007
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Making report from 4 tables details

    I'm trying to get the total of amount sold of the items in the store & the total of cash receipts. I used certain date .. it works find but i face several wrong data.


    i tried this query:
    PHP Code:

    SELECT DISTINCT
      orders
    .idorders_items.orderidorders_items.id AS ITEM_IDorders.useridusers.useridusers.fnameSUM((orders_items.price orders_items.discount) * (orders_items.quantity orders_items.quantity_store)) AS ITEM_PRICEorders_items.`date` AS ITEM_DATEorders_receipts.`date` AS RECEIPT_DATESUM(orders_receipts.amount) AS RECEIPT_AMOUNTorders_receipts.orderidorders_receipts.id AS REC_IDorders_receipts.check_date
    FROM
      orders
      INNER JOIN orders_items ON orders
    .id orders_items.orderid
      INNER JOIN orders_receipts ON orders
    .id orders_receipts.orderid
      INNER JOIN users ON orders
    .userid users.userid
    WHERE
      orders_items
    .`dateBETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`dateBETWEEN '1267390800' AND '1268168400'
    GROUP BY
      orders
    .id
    ORDER BY
      orders
    .id DESC 
    WRONG DATA:
    1- if there is no receipt paid the items sold will not show in the list!
    2- if there are many items sold, the receipts gets multiplied by the number of items!
    3- if there are many receipts, the items price gets multiplied by the number of receipts!

    Note: tables digram attached.
    Attached Images Attached Images


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
  •