Suppose I have the following MySQL table:
user_id date_of_application date_ended grade status
---------------------------------------------------------------
1 2011-01-01 2011-02-28 1.0 Ended
1 2011-02-02 2011-03-28 1.0 Ended
1 2011-03-03 2011-04-28 (1.5) Ended
2 2011-01-01 2011-02-20 2.0 Ended
2 2011-02-02 2011-03-11 2.5 Ended
2 2011-03-03 2011-04-28 (1.0) Ended
1 2011-05-10 - - Pending
2 2011-05-15 - - Pending
What I want to accomplish here is to retrieve all rows (together will all columns) WHERE status is 'Pending' and such that the value for the grade column for each of these retrieved rows is the value of the latest grade (in parenthesis above) where status is 'Ended' for this particular user (or row).
The result:
user_id date_of_application date_ended grade status
---------------------------------------------------------------
1 2011-05-10 2011-06-10 1.5 Pending
2 2011-05-15 2011-06-15 1.0 Pending
----EDIT-----:
I'm not sure if I need to create another thread for these additions (' will do if I'm advised to do so), anyhow -- I decided to go with ypercube's answer (see below) (which works, minus the commented part). However, in addition to the above resulting table, I also would like to select two more columns (each from a different table):
Working code that gives-off the desired table result (see above):
SELECT user_id
, date_of_application
, date_ended
, ( SELECT te.grade
FROM TableX AS te
WHERE te.status = 'Ended'
AND te.user_id = t.user_id
ORDER BY te.date_ended DESC
LIMIT 1
) AS grade
, status
FROM TableX AS t
WHERE status = 'Pending'
Now I need to select these two other columns (is_first_time_user and name) together with the result table previously mentioned. Note that user_id for both of these tables are unique:
Table2:
user_id is_firs_time_user
-----------------------------
1 no
2 no
Table3:
user_id name
----------------------
1 User A
2 User B
SELECT user_d
, date_of_application
, date_ended
, ( SELECT te.grade
FROM TableX AS te
WHERE te.status = 'Ended'
AND te.user_id = t.user_id
AND te.date_ended < t.date_of_application --- not sure if
--- this is needed
ORDER BY te.date_ended DESC
LIMIT 1
) AS grade
, status
FROM TableX AS t
WHERE status = 'Pending'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With