Get Value from INNER JOIN 3 table with LASTEST RECORD from each table

Question

I get problem to get value from some of tables. You can see picture below, I wanna get row what I block with red color.

I try with code below

SELECT p.id, 
       p.email, 
       p.name, 
       p.lastname, 
       p.gender, 
       ex.startwork, 
       ex.endwork, 
       e.degree, 
       e.majority, 
       j.division 
FROM   job_jobseeker AS p 
       INNER JOIN job_experience AS ex 
               ON p.email = (SELECT ex.email 
                             FROM   job_experience 
                             ORDER  BY ex.id DESC 
                             LIMIT  1) 
       INNER JOIN job_education AS e 
               ON p.email = (SELECT e.email 
                             FROM   job_education 
                             ORDER  BY ex.id DESC 
                             LIMIT  1) 
       INNER JOIN job_applying AS j 
               ON p.email = (SELECT j.email 
                             FROM   job_applying 
                             ORDER  BY ex.id DESC 
                             LIMIT  1) 

Answer 1

You need correlated sub-queries.

Find the latest id for each email in all the three tables

SELECT startwork, 
       endwork, 
       email 
FROM   job_experience a 
WHERE  a.id = (SELECT Max(b.id) 
               FROM   job_experience b 
               WHERE  a.email = b.email) 

The above query will find the latest id for each email in job_experience table. Do the same for other two tables as well, then join the result with job_jobseeker table to get the result.

SELECT p.id, 
       p.email, 
       p.name, 
       p.lastname, 
       p.gender, 
       ex.startwork, 
       ex.endwork, 
       e.degree, 
       e.majority, 
       j.division 
FROM   job_jobseeker AS p 
       INNER JOIN (SELECT startwork, 
                          endwork, 
                          email 
                   FROM   job_experience a 
                   WHERE  a.id = (SELECT Max(b.id) FROM job_experience b 
                                  WHERE  a.email = b.email)) AS ex 
               ON p.email = ex.email 
       INNER JOIN (SELECT email,  //Just called column without initialize
                          degree, 
                          majority 
                   FROM   job_education a 
                   WHERE  a.id = (SELECT Max(b.id) FROM job_education b 
                                  WHERE  a.email = b.email)) AS e 
               ON p.email = e.email 
       INNER JOIN (SELECT email, //Just called column without initialize
                          division 
                   FROM   job_applying a 
                   WHERE  a.id = (SELECT Max(b.id) FROM job_applying b 
                                  WHERE  a.email = b.email)) AS j 
               ON p.email = j.email