0

I have two tables and want to join the result from these two tables but my condition here is:

Member Table
+------+----------+--------+----------------------+
| a_id | name     | Active | Date                 |
+------+----------+--------+----------------------+
| 1    | Da Vinci | 1      | 2012-03-27 15:40:14  |
| 2    | Monet    | 0      | 2012-04-16 01:14:17  |
| 3    | Van Gogh | 0      | 2012-03-22 15:49:59  |
| 4    | Picasso  | 1      | 2012-02-27 09:40:05  |
| 5    | Renoir   | 1      | 2012-04-22 15:40:14  |
+------+----------+--------+----------------------+ 

Purchase Table
+------+------+-------------------+---------------------+-------+
| a_id | p_id | title             | purchase date       | price |
+------+------+-------------------+---------------------+-------+
| 1    | 1    | The Last Supper   | 2012-03-27 15:40:14 | 34    |
| 1    | 2    | The Mona Lisa     | 2012-04-27 15:40:14 | 87    |
| 3    | 3    | Starry Night      | 2012-03-27 15:40:14 | 48    |
| 3    | 4    | The Potato Eaters | 2012-03-27 15:40:14 | 67    |
| 3    | 5    | The Rocks         | 2012-03-27 15:40:14 | 33    |
| 5    | 6    | Les Deux Soeurs   | 2012-04-30 15:40:14 | 64    |
+------+------+-------------------+---------------------+-------+

From the tables above I want to find the 2 most recent active members (member table) and their last 1 purchase made from purchase table. Its result should looks like:

+------+------+-------------------+---------------------+-------+
| a_id | p_id | title             | purchase date       | price |
+------+------+-------------------+---------------------+-------+
| 1    | 2    | The Mona Lisa     | 2012-04-27 15:40:14 | 87    |
| 5    | 6    | Les Deux Soeurs   | 2012-04-30 15:40:14 | 64    |
+------+------+-------------------+---------------------+-------+

I have tried hours to find the answer to this question but have not found any solution. Please help me.

Thank you for your support.

MahiloDai
  • 63
  • 1
  • 3
  • 10

1 Answers1

1

try 

SELECT a.a_id, p.p_id, p.title, p.purchase_date, p.price 
FROM Member a LEFT JOIN Purchase p ON a.a_id = p.a_id 
WHERE a.Active = 1 
ORDER BY a.Date DESC LIMIT 2

Change

ORDER BY a.Date DESC

to

ORDER BY a.Date DESC, p.purchase_date DESC
lusketeer
  • 1,890
  • 1
  • 12
  • 29
  • Thank You Very Much for your solution. Here my question is, how do I limit the value extracted from Purchase Table by 1 which also should be ordered by Date in DESC. To be more specific a_id 1 has made 2 purchases (p_id 1 and 2). Now it should only extract values from p_id 2 as most recent purchase made by a_id 1. – MahiloDai Jul 14 '12 at 02:00
  • 1
    you can modify the criteria after Order By the order you want. In this case, you wanna sort the member joined date first, then the purchase date for each individual member. Refer to [this](http://stackoverflow.com/questions/2051162/sql-multiple-column-ordering) – lusketeer Jul 14 '12 at 19:28