StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySql select all rows in one table based on MAX value in another table
text
Body
copied!<p>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.</p> <p><strong>Table 1: applications</strong></p> <pre><code>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 </code></pre> <p><strong>Table 2: application_progress</strong></p> <pre><code>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) </code></pre> <p><strong>EDIT: third table</strong></p> <p><strong>Table 3: applicants</strong></p> <pre><code>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 </code></pre> <p><strong>Returned data set</strong></p> <pre><code>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 </code></pre> <p>Am struggling with this one and would appreciate any help. </p> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload