Bashabi Bashabi - 6 months ago 18
MySQL Question

SQL server select distinct latest values by inner joining three or more table

In my data base I have these tables each and every table has nearly 200,000 rows in it.

User table:

╔════════╦══════╦═════════════╗
║ UserID ║ Name ║ Email ║
╠════════╬══════╬═════════════╣
║ 1 ║ ABC ║ abc@abc.com ║
║ 2 ║ DEF ║ def@def.com ║
║ 3 ║ XYZ ║ xyz@xyz.com ║
╚════════╩══════╩═════════════╝


Vacancy table:

╔════════════╦═════════════╗
║ VacancyId ║ VacancyName ║
╠════════════╬═════════════╣
║ 1 ║ Vacancy 1 ║
║ 2 ║ Vacancy 2 ║
║ 3 ║ Vacancy 3 ║
╚════════════╩═════════════╝


Vacancy Application table:

╔══════════════════════╦═══════════╦══════════════╦══════════════════╗
║ VacancyApplicationID ║ VacancyId ║ UserID ║ Application Date ║
╠══════════════════════╬═══════════╬══════════════╬══════════════════╣
║ 1 ║ 1 ║ 1 ║ 2009-01-01 ║
║ 2 ║ 1 ║ 2 ║ 2009-01-02 ║
║ 3 ║ 2 ║ 1 ║ 2010-01-02 ║
║ 4 ║ 3 ║ 1 ║ 2011-02-02 ║
║ 5 ║ 2 ║ 2 ║ 2010-03-04 ║
║ 6 ║ 1 ║ 3 ║ 2009-04-07 ║
╚══════════════════════╩═══════════╩══════════════╩══════════════════╝


I am trying to combine some of the data together. I want to obtain the distinct user id and their latest applied vacancy in one grid, like this:

Expected result:

╔════════╦══════╦═════════════╦══════════════════════╗
║ UserID ║ Name ║ Email ║ Last applied vacancy ║
╠════════╬══════╬═════════════╬══════════════════════╣
║ 1 ║ ABC ║ abc@abc.com ║ Vacancy 3 ║
║ 2 ║ DEF ║ def@def.com ║ Vacancy 2 ║
║ 3 ║ XYZ ║ xyz@xyz.com ║ Vacancy 1 ║
╚════════╩══════╩═════════════╩══════════════════════╝


Now some of the users did not apply for any vacancy. For them I just want to put "N/A" in the last applied vacancy column. How can I do that?

I have tried using the SQL statement below, but it duplicates same user id data:

SELECT DISTINCT
c.[id],
va.[candidateid],
va.[vacancyid]
FROM user C
INNER JOIN VacancyApplication VA ON c.userid=va.userid
ORDER BY va.application_date DESC

Answer

Try the following query. It includes a subquery on the vacancy application table to restrict to the most recent vacancy for each user. In addition, it uses COALESCE() on the application date in the event that a given user does not have any applications.

SELECT u.UserID, u.Name, u.Email, COALESCE(v.VacancyName, '') AS `Last applied vacancy`
FROM User u
LEFT JOIN
vacancy_application v1
    ON u.UserID = v1.UserID
INNER JOIN
Vacancy v
    ON v1.VacancyId = v1.VacancyId
INNER JOIN
(
    SELECT UserID, MAX(ApplicationDate) AS maxDate
    FROM vacancy_application
    GROUP BY UserID
) v2
    ON v1.UserID = v2.UserID AND v1.ApplicationDate = v2.maxDate

Click the link below for a running demo:

SQLFiddle

Comments