MySql根据另一个表中的最大值选择一个表中的所有行

I want to be able to get all the data from table 1 and table 3 below but in addition to this I also want to get the latest application stage from table 2. The latest application stage is determined by getting the max stage_date for each application.

我希望能够从表1和表3中获取所有数据，但除此之外，我还想从表2获得最新的应用程序阶段。最新的应用程序阶段由获取每个应用程序的最大stage_date决定。

Table 1: applications

表1:应用程序

id | applicant_id | col_x | col_y | col_z
-----------------------------------------
10   300            a       b       c    
11   310            a       b       c    
12   320            a       b       c    
13   330            a       b       c    
14   340            a       b       c

Table 2: application_progress

表2:application_progress

id | application_id | application_stage | stage_date | stage_notes
------------------------------------------------------------------
1    10               DRAFT               2013-01-01   (NULL)   
2    10               APPLICATION         2013-01-14   (NULL)   
3    10               PHASE1              2013-01-30   (NULL)
4    11               DRAFT               2013-01-01   (NULL)  
4    12               DRAFT               2013-01-01   (NULL) 
5    13               DRAFT               2013-01-01   (NULL)  
6    14               DRAFT               2013-01-01   (NULL)   
7    14               APPLICATION         2013-01-14   (NULL)

EDIT: third table

编辑:第三个表

Table 3: applicants

表3:申请人

id  | applicant_name | applicant_address | programme_id 
------------------------------------------------------
300   Applicant 1      abc                 1    
310   Applicant 2      xyz                 2    
320   Applicant 3      xyz                 2    
330   Applicant 4      xyz                 2    
340   Applicant 5      xyz                 2

Returned data set

返回数据集

applicant_id | applicant_name | current_stage
---------------------------------------------------------
300            Applicant 1      PHASE1
310            Applicant 2      DRAFT
320            Applicant 3      DRAFT 
330            Applicant 4      DRAFT   
340            Applicant 5      APPLICATION

Am struggling with this one and would appreciate any help.

我正在与这一个挣扎，并将感激任何帮助。

PS. Tried to put an example of sqlfiddle but it's down at the minute. I'll update this with the sqlfiddle when it's back up if haven't had an answer before this.

PS，试着放一个sqlfiddle的例子，但是现在它已经停止了。如果在此之前没有答案，我将在备份时用sqlfiddle更新它。

1 个解决方案

#1

You can do this with a correlated subquery:

可以使用相关子查询:

select a.*,
       (select application_stage
        from application_progress ap
        where ap.application_id = a.id
        order by stage_date desc
        limit 1
       ) MostRecentStage
from applications a;

EDIT:

编辑:

You can joining in the applicant data with something like this::

你可以加入申请人的资料如下:

select a.*, aa.*,
       (select application_stage
        from application_progress ap
        where ap.application_id = a.id
        order by stage_date desc
        limit 1
       ) MostRecentStage
from applications a join
     applicant aa
     on a.applicant_id = aa.id;

#1