php - MySQL JOIN and DATE comparison/manipulation

Answer

------ 1 user_a 2010-09-09 2010-12-24 Ended 2 user_a 2011-03-03 2011-06-06 Ended 3 user_a 2011-08-08 2011-10-10 Ended 4 user_b 2011-10-01 Active|||user_id | item_id | quantity_ordered | date_requested | date_received

Answer

------ user_a item_a 20 2011-10-01 2011-10-02 user_a item_b 10 2011-10-01 2011-10-02 user_b item_a 30 2011-10-05 2011-10-10 user_b item_b 30 2011-10-05 2011-10-10|||item_id | item_name | item_price

Answer

------ item_a Item AA 500 item_b Item BB 1000|||SELECT item_po.poid, SUM(item_transactions.quantity_ordered) AS qtysum, item_transactions.item_id, item_transactions.quantity_ordered, items.item_id, items.item_price, applications.user_id, applications.status FROM items WHERE applications.status='Ended' GROUP BY item_transactions.user_id

Answer

Solution:

To select only the last ended application for each user, you can use a subquery to select the IDs of those applications and join the applications table with that subquery. Assuming that application IDs are ordered chronologically:

SELECT
  item_po.poid,
  SUM(item_transactions.quantity_ordered) AS qtysum,
  item_transactions.item_id,
  item_transactions.quantity_ordered,
  items.item_id,
  items.item_price,
  applications.user_id,
  applications.status   
FROM
  applications
  NATURAL JOIN (
    SELECT
      MAX(unique_id) AS unique_id
    FROM applications
    WHERE status = 'Ended'
    GROUP BY user_id
  ) AS foo
  /* ... other joins here ... */
Copy code

However, I couldn't figure out from your description exactly how the transactions should be joined to the applications. I assume you probably want something like this:

  JOIN item_transactions
    ON date_requested BETWEEN date_of_application AND date_ended
  NATURAL JOIN items
Copy code

but I'm not sure about the exact join criteria. (For example, what if the item was requested during the application period but delivered after its end? Or requested before but delivered during the application period?)

Answer

Solution:

you can try this.

SELECT

    SUM(Tran.quantity_ordered) AS qtysum,
    Tran.item_id,
    Tran.quantity_ordered,

    I.item_id,
    I.item_price,

    A.user_id,
    A.status

FROM
    items as I,applications as A, item_transactions AS Tran

WHERE
    A.status='Ended'

GROUP BY
    Tran.user_id

    ORDER BY A.unique_id DESC 

LIMIT 0,1
Copy code

If you are looking for only one latest record then you need to use LIMIT 0,1 otherwise its working without that..i think this would be helpful to you.

Thanks.

Answer

Solution:

how about if you try this one:

SELECT
    item_po.poid,
    SUM(item_transactions.quantity_ordered) AS qtysum,
    item_transactions.item_id,
    item_transactions.quantity_ordered,

    items.item_id,
    items.item_price,

    applications.user_id,
    applications.status

FROM
    items

WHERE
    applications.status='Ended'
AND applications.date_of_application = (SELECT MAX(date_of_application) FROM applications
WHERE status='Ended' LIMIT 0, 1)

GROUP BY
    item_transactions.user_id
Copy code

Since you need the latest application you should get the latest date of all the ended applications no matter when it did ended, but I'm having doubt on this statement "that its user's status has Ended and that application being the latest one." cause I was thinking you might be saying that once a transaction has ended that will turn the transaction as the latest one no matter when it did started being there on the record if that is the case instead of using the date_of_application attribute you may somehow use date_ended instead.

I hope this helps.

:)