CODEWITHSUNDEEP
sqlpostgresql
Noufal Ibrahim
Noufal Ibrahim

Reputation: 72755

Limiting subqueries in SQL

I have a situation which is a little hard to describe. I'll try to explain with an example and the result which I want.

I have three tables like so

 Employee

 | id | Name  |
 |----+-------|
 |  1 | Alice |
 |  2 | Bob   |
 |  3 | Jane  |
 |  4 | Jack  |


 Task

 | id | employee_id | description         |
 |----+-------------+---------------------|
 |  1 |           1 | Fix bug             |
 |  2 |           1 | Implement feature   |
 |  3 |           1 | Deploy master       |
 |  4 |           2 | Integrate feature   |
 |  5 |           2 | Fix cosmetic issues |


 Status

 | id | task_id |  time | details   | Terminal |
 |----+---------+-------+-----------+----------|
 |  1 |       1 | 12:00 | Assigned  | false    |
 |  2 |       1 | 12:30 | Started   | false    |
 |  3 |       1 | 13:00 | Completed | true     |
 |  4 |       2 | 12:10 | Assigned  | false    |
 |  5 |       2 | 14:00 | Started   | false    |
 |  6 |       3 | 12:15 | Assigned  | false    |
 |  7 |       4 | 12:20 | Assigned  | false    |
 |  8 |       5 | 12:25 | Assigned  | false    |
 |  9 |       4 | 12:30 | Started   | false    |

(I have also put these into a sqlfiddle page at http://sqlfiddle.com/#!9/728c85/1)

The basic idea is that I have some employees and tasks. The tasks can be assigned to employees and as they work on them, they keep adding "status" rows.

Each status entry has a field "terminal" which can either be true or false.

If the last status entry for a task has terminal set to true, then that task is over and there's nothing more to be done on it.

If all tasks assigned to an employee are over, then the employee is considered free.

I need to get a list of free employees. This would basically mean, given an employee, a list of all his or her tasks with statuses. So, something like this for Alice

 | Task              | Completed |
 |-------------------+-----------|
 | Fix bug           | true      |
 | Implement feature | false     |
 | Deploy master     | false     |

From which I know that she's not free right now since there are 'false' entries in completed.

How would I do this? If my tables are not constructed properly for this kind of query, I'd very much like some advice on that too.

(I titled the question like this since I want to order the statuses of each task per user and them limit them to the last row).

Update

It was suggested to me that the status field should really go inside the tasks table and the Status table should simple be a log table.

Upvotes: 1

Views: 816

Answers (4)

Darshak
Darshak

Reputation: 867

I hope this will help you...??

    select E.Name,T.id as[Task Id],T.description,S.Terminal from Employee E 
inner join Task T on E.id=T.employee_id 
inner join Status S on S.task_id=T.id 
where  e.id not in (select employee_id from Task where id in (select task_id from Status where Terminal='true' and details='Completed') )

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

I would go for the idea to have the status in the tasks table. (Please see my comment on your request on this.) However, here are two queries to select free employees:

If tasks cannot be re-opened, it is simple: Get all incompleted tasks by checking whether a record with terminal = true exists. Free employees are all that have no incomplete task.

select *
from employee
where id not in
(
  select employee_id
  from task
  where id not in (select task_id from status where terminal = true)
);

If tasks can be re-opened, however, then you do the same but must find the last status. This can be done with Postgre's DISTINCT ON for instance.

select *
from employee
where id not in
(
  select employee_id
  from task
  where not 
  (
    select distinct on (task_id) terminal 
    from status 
    where task_id = task.id
    order by task_id, id desc
  )
);

(I am using the ID to find the last entry per task, as the time without a date seems inappropriate. You could only use the time column instead, if a task will always run within one day only.)

SQL fiddles:

Upvotes: 3

Ankur
Ankur

Reputation: 3199

Hope this helps

select T.employee_id, T.description, S.Terminal
from Employee E
INNER JOIN Task T ON E.id=T.employee_id
INNER JOIN (Select task_id, max(id) as status_id FROM Status GROUP BY task_id) as ST on T.id=ST.task_id 
INNER JOIN Status S on S.id=ST.status_id

Upvotes: 0

cromir
cromir

Reputation: 711

You have to group all the statuses togheter and you can then use MAX() to find if one of them is true, like this:

SELECT t.description,  MAX(s.terminal)
FROM Employee e
INNER JOIN task t ON t.employee_id = e.id
INNER JOIN status s ON s.task_id = t.id
GROUP BY t.id;

When you want this just for one user add something like this WHERE e.id = 1.

Upvotes: 0

Related Questions