CODEWITHSUNDEEP
mysqlsorting
Jeroen Bellemans
Jeroen Bellemans

Reputation: 2035

sql sort on 2 tables same column name

I have 2 tables:

  1. projects_tasks
  2. projects_tasks_deadlines

Now I have a simple query where I sort each task on their deadline. At first a task could only have 1 deadline.

Then I got asked to be able to set multiple deadlines on a single task. That's why I have the second table projects_tasks_deadlines.

I've managed to allow the user to choose which deadline should be activated (task detail). But in the list with all the tasks, I do not know how to sort between 2 tables, if that's at least possible.

This is the query I'm currently using: 

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.deadline AS task_deadline,
    projects_tasks.created_by AS task_creator,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname
FROM `projects_tasks`
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
WHERE projects_tasks.project_id = :project_id
AND labels.id != '8'
ORDER BY -projects_tasks.deadline DESC

How can I achieve my goal, being checking if in the second table are deadlines active, with the respective `task_id. If matches are found take that deadline into count and sort..

An example:

Table projects_tasks:

id | name | deadline
--------------------
1  | test | 2016-01-19
2  | test2| 2016-02-15
3  | test | 2016-01-25

Table projects_tasks_deadlines

id | task_id | deadline  | active
--------------------------------
1  | 1       | 2016-01-20| 1 // yes
2  | 1       | 2016-01-24| 0 // no
3  | 2       | 2016-02-25| 0

Result should be

  1. 2016-01-20 -> projects_tasks_deadlines
  2. 2016-01-25 -> projects_tasks
  3. 2016-02-15 -> projects_tasks

Thanks for the help, hope this is clear enough...

** UPDATE **

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.created_by AS task_creator,
    projects_tasks_deadlines.id AS deadline_id,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname,
    CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN projects_tasks_deadlines.deadline AS task_deadline ELSE
    projects_tasks.deadline AS task_deadline END
FROM `projects_tasks`
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
    LEFT JOIN projects_tasks_deadlines ON projects_tasks_deadlines.task_id = projects_tasks.id
WHERE projects_tasks.project_id = '1'
AND labels.id != '8'
ORDER BY CASE WHEN projects_tasks_deadlines.deadline_active = 1 THEN -projects_tasks_deadlines.deadline ELSE -projects_tasks.deadline END DESC

How should I do the CASE in the SELECT?

Upvotes: 1

Views: 51

Answers (2)

Jeroen Bellemans
Jeroen Bellemans

Reputation: 2035

I've finaly found the solution:

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.created_by AS task_creator,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname,
    (CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN projects_tasks_deadlines.deadline ELSE projects_tasks.deadline END) AS task_deadline
FROM projects_tasks
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
    LEFT JOIN projects_tasks_deadlines ON projects_tasks_deadlines.task_id = projects_tasks.id
WHERE projects_tasks.project_id = '1'
AND labels.id != '8'
GROUP BY projects_tasks.id
ORDER BY CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN -projects_tasks_deadlines.deadline ELSE -projects_tasks.deadline END DESC

I was missing another CASE in the SELECT:

(CASE WHEN projects_tasks_deadlines.deadline_active = '1' THEN projects_tasks_deadlines.deadline ELSE projects_tasks.deadline END) AS task_deadline

This filtered out the right deadline, and they got sorted on the right deadline too.

Upvotes: 0

bdifferent
bdifferent

Reputation: 703

You can do a conditional order by. If active = 1 then use the deadline date from projects_tasks_deadline table, otherwise from projects_tasks table. So something like this:

SELECT 
    projects_tasks.id AS task_id,
    projects_tasks.name AS task_name,
    projects_tasks.deadline AS task_deadline,
    projects_tasks.created_by AS task_creator,
    labels.name AS task_label,
    users.name AS task_responsible_name,
    users.surname AS task_responsible_surname,
    users2.name AS task_assigner_name,
    users2.surname AS task_assigner_surname
FROM `projects_tasks` 
    INNER JOIN `labels` ON labels.id = projects_tasks.label_id
    INNER JOIN `users` ON users.id = projects_tasks.responsible_id
    INNER JOIN `users` AS `users2` ON users2.id = projects_tasks.created_by
LEFT JOIN projects_tasks_deadline ptd ON id = task_id
WHERE projects_tasks.project_id = :project_id
AND labels.id != '8'
ORDER BY CASE WHEN ptd.active = 1 THEN ptd.deadline ELSE projects_tasks.deadline END DESC

Hope that makes sense?

Upvotes: 2

Related Questions