CODEWITHSUNDEEP
mysqlsqljoinleft-join
mesqueeb
mesqueeb

Reputation: 6324

How to JOIN two tables based on a WHERE that uses a complex statement in SQL

I have 2 tables: 1 with tasks, 1 with task_actions.

I'd like to have the task table with some info about the task_actions column. That works fine when I use a LEFT JOIN like this:

SELECT 
    *,  wp_task_mgr.id AS task_id,
    wp_task_actions.id AS action_id,
    wp_task_actions.completion_percentage AS lastcompperc
FROM 
    wp_task_mgr
LEFT JOIN wp_task_actions
    ON wp_task_mgr.id = wp_task_actions.id_task

However, the problem is: I get the oldest actions towards my tasks with this query, and not the most recent.

I have sought for a long time for getting the most recent records grouped on a certain column and I was able to get the most recent records.

I was able to get the most recent actions grouped per task_id. So search for 1 task_id and get the most recent action in the table:

SELECT * 
FROM wp_task_actions
INNER JOIN
   (SELECT id_task, MAX(created_taskaction) AS MaxDateTime
    FROM wp_task_actions
    GROUP BY id_task) mostrecent ON wp_task_actions.id_task = mostrecent.id_task
AND wp_task_actions.created_taskaction = mostrecent.MaxDateTime");

Now I need to combine these two queries. I need my full task table with one column from the actions which shows the most recent action for that task_id. (namely task_actions.completion_percentage)

My desired table looks like:

task_mgr.id | task_mgr.name | task_actions.completion_percentage

(which is the last record towards this task_mgr.id or task_actions.task_id)

Any ideas on how to get this?

Cheers!

Upvotes: 0

Views: 59

Answers (3)

Balaji
Balaji

Reputation: 1

While getting the max date could be a sub query, and restrict the group by to that specific 'id_task' will improve the performance


SELECT 
    *,  wp_task_mgr.id AS task_id,
    wp_task_actions.id AS action_id,
    wp_task_actions.completion_percentage AS lastcompperc
FROM 
    wp_task_mgr
LEFT JOIN wp_task_actions 
    ON wp_task_mgr.id = wp_task_actions.id_task
where
    wp_task_actions.created_taskaction in (
    SELECT MAX(created_taskaction) AS MaxDateTime
    FROM wp_task_actions wta
    where wta.id_task = wp_task_actions.id_task
    GROUP BY id_task)

Upvotes: 0

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

To solve that, MS SQL 2005+ use OUTER APPLY, but I don't sure MySQL can use it:

SELECT 
    wp_task_mgr.*,
    wp_task_mgr.id AS task_id,
    A.id AS action_id,
    A.lastcompperc
FROM 
    wp_task_mgr
OUTER APPLY (
    SELECT DISTINCT id, id_task, 
         (SELECT MAX(created_taskaction) AS lastcompperc
          FROM wp_task_actions WHERE id_task = wp.id_task)
    FROM wp_task_actions wp
    WHERE wp_task_mgr.id = wp_task_actions.id_task
) AS A

But, you can use LEFT JOIN:

SELECT 
    wp_task_mgr.*,
    wp_task_mgr.id AS task_id,
    A.id AS action_id,
    A.lastcompperc
FROM 
    wp_task_mgr
LEFT JOIN (
    SELECT DISTINCT id, id_task, 
         (SELECT MAX(created_taskaction) AS lastcompperc
          FROM wp_task_actions WHERE id_task = wp.id_task)
    FROM wp_task_actions wp
) AS A
ON wp_task_mgr.id = A.id_task

Upvotes: 1

mal-wan
mal-wan

Reputation: 4476

Have you tried quite literally combining your queries? LEFT JOIN your Max Date list to the wp_task_mgr and then LEFT JOIN on the Max Date list to subset the details to only ones that match the Max Date query result?

eg:

SELECT 
    wp_task_mgr.id AS task_id,
    wp_task_actions.id AS action_id,
    wp_task_actions.completion_percentage AS lastcompperc
FROM 
    wp_task_mgr
LEFT JOIN
    (SELECT id_task, MAX(created_taskaction) AS MaxDateTime
        FROM wp_task_actions
        GROUP BY id_task) mostrecent
    ON wp_task_mgr.id = mostrecent.id_task
LEFT JOIN wp_task_actions
    ON mostrecent.id_task = wp_task_actions.id_task
    AND wp_task_actions.created_taskaction = mostrecent.MaxDateTime

Apologies for formatting, on mobile.

Upvotes: 0

Related Questions