CODEWITHSUNDEEP
mysql
Nightwolf
Nightwolf

Reputation: 951

Mysql getting the min value of one field as long as the other field is equal to 1

I have a complicated query I wrote and it did initially retrieve all the data I would like from it. Now I realize I require the one field's value slightly different than what I have it now.

I have : IF(MIN(tasks.due_date) < NOW(),1,0) AS taskoverdue and I would like to have it:

IF(MIN(tasks.due_date WHERE tasks.completed != 1) < NOW(),1,0) AS taskoverdue

I know this syntax will not work, is there any manipulation anyone can think of that would achieve this result?

Due to my query being complicated I cannot alter the where clause or the group by clause, and I cannot run a sub-query within the select clause without considerably killing my application's speed. Oh and I am using php but I am looking for a mysql solution.

Here is my full query just for kicks:

SELECT 
    tickets.id,
    tickets.user_id,
    tickets.name,
    COUNT(DISTINCT tasks.id) AS numTasks,
    IF(MIN(tasks.due_date) < NOW(),1,0) AS taskoverdue,
    IF(tickets.due_date < NOW(),1,0) AS ticketoverdue,
    tasks.queue_id AS queue_id ,
    IF(MIN(tasksteps.time_due) < NOW(),1,0) AS taskstepoverdue,
    COUNT(DISTINCT ticketnotes.ticket_id) as notes
FROM 
    tickets
    LEFT OUTER JOIN ticketsinqueues ON tickets.id = ticketsinqueues.ticket_id
    LEFT OUTER JOIN tasks ON tickets.id = tasks.ticket_id
    LEFT OUTER JOIN tasksteps ON tasks.id = tasksteps.task_id 
    LEFT OUTER JOIN ticketnotes ON tickets.id = ticketnotes.ticket_id
WHERE
    ticketsinqueues.queue_id != tasks.queue_id AND
    tickets.status = (var decided on run time)
GROUP BY
    tickets.id,tasks.queue_id
UNION
SELECT 
    tickets.id,
    tickets.user_id,
    tickets.name,
    COUNT(DISTINCT tasks.id) AS numTasks,
    IF(MIN(tasks.due_date) < NOW(),1,0) AS taskoverdue,
    IF(tickets.due_date < NOW(),1,0) AS ticketoverdue,
    tasks.queue_id AS queue_id ,
    IF(MIN(tasksteps.time_due) < NOW(),1,0) AS taskstepoverdue,
    COUNT(DISTINCT ticketnotes.ticket_id) as notes
FROM 
    tickets
    LEFT OUTER JOIN ticketsinqueues ON tickets.id = ticketsinqueues.ticket_id
    LEFT OUTER JOIN tasks ON tickets.id = tasks.ticket_id
    LEFT OUTER JOIN tasksteps ON tasks.id = tasksteps.task_id 
    LEFT OUTER JOIN ticketnotes ON tickets.id = ticketnotes.ticket_id
WHERE
    ticketsinqueues.queue_id = tasks.queue_id AND
    tickets.status = (var decided on run time)
GROUP BY
    tickets.id,tasks.queue_id
UNION
SELECT 
    tickets.id,
    tickets.user_id,
    tickets.name,
    0 AS numTasks,
    0 AS taskoverdue,
    IF(tickets.due_date < NOW(),1,0) AS ticketoverdue,
    ticketsinqueues.queue_id AS queue_id ,
    0 AS taskstepoverdue,
    0 AS notes
FROM 
    tickets 
    LEFT OUTER JOIN ticketsinqueues ON tickets.id = ticketsinqueues.ticket_id
WHERE 
tickets.status = (var decided on run time) AND
NOT EXISTS (
    SELECT 
        * 
    FROM 
        tasks 
    WHERE 
        tickets.id = tasks.ticket_id AND 
        tickets.queue_id = tasks.queue_id 
    GROUP BY 
        tasks.ticket_id,
        tasks.queue_id)
ORDER BY id desc

Any help will be appreciated.

Upvotes: 1

Views: 374

Answers (1)

Mark Byers
Mark Byers

Reputation: 838266

MAX(tasks.completed != 1 AND tasks.due_date < NOW()) AS taskoverdue

Upvotes: 2

Related Questions