CODEWITHSUNDEEP
mysqlsql
Mario Cordeiro
Mario Cordeiro

Reputation: 125

Join 3 tables, LIMIT 1 on second table

I have 3 tables, persons, companies and tasks.

Persons make different tasks in different companies.

What I want is a list of ALL the persons table, the last task they have in tasks and the name of the company when they do that task. The most recent task could be the newest task_date or the higher id.tasks, it has the same result.

Table persons:

| id  |   Name     |
| 1   |  Person 1  |
| 2   |  Person 2  |
| 3   |  Person 3  |
| 4   |  Person 4  |

Table companies:

—————————————————————
| id  |   company   |
—————————————————————
| 1   |  Company 1  |
| 2   |  Company 2  |
| 3   |  Company 3  |
| 4   |  Company 4  |
—————————————————————

Table tasks:

————————————————————————————————————————————————————————————————————
| id  |   task_name |  task_date    |  id_persons  |  id_companies |
————————————————————————————————————————————————————————————————————
| 1   |  Task 1     |   2015-01-02  |       1      |          3    |
| 2   |  Task 2     |   2016-03-02  |       1      |          4    |
| 3   |  Task 3     |   2016-06-04  |       2      |          1    |
| 4   |  Task 4     |   2016-01-03  |       4      |          2    |

The result should be a table like this:

| persons.id  |   persons.name  |  company.name  |
|       1     |  Person 1       |   Company 4    | 
|       2     |  Person 2       |   Company 1    |  
|       3     |  Person 3       |                |  
|       4     |  Person 4       |   Company 2    |  
——————————————————————————————————————————————————

I have this query:

SELECT t.id id_t, t.id_companies t_id_companies, c.company_name , p.*
FROM persons p
INNER JOIN tasks t ON t.id_persons = p.id
INNER JOIN 
        (
            SELECT id_persons, MAX(id) max_id
            FROM tasks
            GROUP BY id_persons
        ) b ON t.id_persons = b.id_persons AND t.id = b.max_id
INNER JOIN companies c ON c.id = t.companies.id
WHERE p.deleted = 0

I think the result is ok, but there some missing persons, because the use of INNER JOIN (I have persons that doesn’t have any task associated). I’ve tried to change INNER JOIN to LEFT JOIN but the result was not ok.

Any help is appreciated.

Upvotes: 0

Views: 376

Answers (5)

trincot
trincot

Reputation: 351348

The solution is in the LEFT JOIN, but you need to first join the sub query, and only then the tasks table, otherwise you get too many results (I also fixed some typos in your query):

SELECT     p.id persons_id, p.name persons_name, c.company_name
FROM       persons p
LEFT JOIN 
        (
            SELECT id_persons, MAX(id) max_id
            FROM tasks
            GROUP BY id_persons
        ) b ON p.id = b.id_persons
LEFT JOIN  tasks t ON t.id_persons = p.id AND t.id = b.max_id
LEFT JOIN  companies c ON c.id = t.id_companies
WHERE      p.deleted = 0
ORDER BY   1

Output is exactly as you listed in your question:

| id |     Name | company_name |
|----|----------|--------------|
|  1 | Person 1 |    Company 4 |
|  2 | Person 2 |    Company 1 |
|  3 | Person 3 |       (null) |
|  4 | Person 4 |    Company 2 |

Here is a fiddle

Upvotes: 1

Hasham
Hasham

Reputation: 34

little tweak in the answer given by John Wyss, this query just clarifies the answer provided by him... hope this will help as well

SELECT p.*, combined_tasks.companyname
FROM person p
LEFT JOIN (
    SELECT t.personid as personid, c.name as companyname FROM task t
    INNER JOIN company c ON c.id = t.companyid
    WHERE t.id IN (SELECT max(id) FROM task GROUP BY personid)
) combined_tasks
ON p.id = combined_tasks.personid

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271091

Given your query, I would just do a simple modification:

SELECT t.id id_t, t.id_companies t_id_companies, c.company_name , p.*
FROM persons p LEFT JOIN
     tasks t
     ON t.id_persons = p.id LEFT JOIN 
     (SELECT id_persons, MAX(id) as max_id
      FROM tasks
      GROUP BY id_persons
     ) b
     ON t.id_persons = b.id_persons AND t.id = b.max_id LEFT JOIN
     companies c
     ON c.id = t.companies.id
WHERE p.deleted = 0;

In other words, all the JOINs should be LEFT JOINs. That is the only change needed to your query.

Upvotes: 0

John Wyss
John Wyss

Reputation: 144

What you should do is make the latest task/company data an inline view, then do a left join to that from the person table.

SELECT *
FROM persons p
LEFT JOIN (
    SELECT * FROM tasks t
    INNER JOIN companies c ON c.id = t.companies.id
    WHERE t.id IN (SELECT max(id) FROM tasks GROUP BY id_persons)
) combined_tasks
ON p.id = combined_tasks.id
WHERE p.deleted = 0

Upvotes: 1

Ing. Gerardo Sánchez
Ing. Gerardo Sánchez

Reputation: 1667

Try this:

SELECT p.id, p.name, c.name
FROM persons p
LEFT JOIN ((
        SELECT id_persons, id_companies, MAX(id) max_id
        FROM tasks
        GROUP BY id_persons, id_companies
    ) t INNER JOIN companies c ON c.id = t.companies.id)
ON t.id_persons = p.id
WHERE p.deleted = 0

Upvotes: 0

Related Questions