Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

3 table join question

Hi,
I need some help on a query of 3 tables, doing joins I think.
The tables and fields are as follows
task
   tskid
   tskname
   priority
   dlndate
   estdate
   pctcmpl
   
subtask
   subid
   tskid
   subname
   priority
   dlndate
   estdate
   pctcmpl

notes
   noteid
   tskid
   subid
   notes
   adddate

I need to display all the tasks and their subtasks along with the latest notes by adddate for the tasks and subtasks. There may not be any notes for either.
Example

Task1, priority 1, 03-28-03, 03-28-03, 50%, almost done with task.
Sub of Task1, priority 4, 03-27-03, 03-28-03, 90%,
Task2, priority 2, 03-28-03, 03-28-03, 50%, almost done with task.
Task3, priority 1, 03-28-03, 03-28-03, 100%, done with task.

This is for oracle.
Thanks
John

 
0
johnc83
Asked:
johnc83
  • 2
  • 2
1 Solution
 
Binary1Commented:
It looks like you are actually combining two separate queries into one output: task and notes, subtasks and notes.

You should be able to get the information you are wanting with something like the following outer join/union statement.

I'm making some assumptions here such as notes for tasks will have null for the subtask id and that your id numbering for subtasks does not include 0. I am also assuming that you have tasks with no subtasks. Obviously if these assumptions are incorrect the statement will need to be modified.


select t.tskid, 0, t.tskname, t.priority,
       t.dlndate, t.estdate, t.pctcmpl,
       n.notes, n.adddate
  from task t, notes n
 where t.tskid = n.tskid (+)
   and n.subid = null

union all

select t.tskid, s.subid, s.subname, s.priority,
       s.dlndate, s.estdate, s.pctcmpl,
       n.notes, n.adddate
  from task t, subtask s, notes n
 where t.tskid = s.tskid
   and s.tskid = n.tskid (+)
   and s.subid = n.subid (+)

order by 1, 2, 9


Is this the results you are looking for?
0
 
johnc83Author Commented:
With the query you put I only get one record back and its a subtask. The only one by the way. If I remove
  and n.subid = null
from the query I get all the records, but also every note entered for each task. So I added the following line in place of the one removed.

AND n.adddate = (SELECT MAX(NOTES.ADDDATE) FROM NOTES WHERE t.tskid = n.tskid)

That seems to give the desired output. But one new field was added to the task table called deptid. This would allow all the tasks to be grouped by deptid. I made the adjustment to the query and can sort by deptid, but also need to sort by priority so the highest one is at the top of each dept. Ok can do that also. But if i want the subtask to show up below its task, I have a problem. I tried to order by all 3 but only get order by the first 2. This gives what I need except any subtask wouldn't be below its task unless the priority was the same. Here is the modified query.

select t.tskid, 0, t.tskname, t.priority, t.dptid,  
      t.dlndate, t.estdate, t.pctcmpl,  
      n.notes, n.adddate  
 from task t, notes n  
where t.tskid = n.tskid (+)  
AND n.adddate = (SELECT MAX(NOTES.ADDDATE) FROM NOTES WHERE   t.tskid = n.tskid)

union all  
 
select t.tskid, s.subid, s.subname, s.priority, t.dptid,
      s.dlndate, s.estdate, s.pctcmpl,  
      n.notes, n.adddate
 from task t, subtask s, notes n  
where t.tskid = s.tskid  
  and s.tskid = n.tskid (+)  
  and s.subid = n.subid (+)  
 
order by 5, 4, 1


Thanks
0
 
Binary1Commented:
It looks like the priority ordering is overriding the task ID ordering. We will want to order the task and then the sub-tasks (if any) below it within the priority of the task.

Try this modified query and let me know how it works. I've separated out the task and subtask priorities as separate columns. The first priority is the priority for the task id and is used for ordering while the second priority in the query is the priority of the returned task/sub-task. This will allow you to use that second priority column field position for the displayed priority, either task or subtask, without having to do any subsequent conditional coding.

The ordering would then by deptid, task priority, task id, subtask id. I've changed the order of selected columns just to make the ordering a little easier to read.

I'm a little confused with the note query for the task. How do you differentiate between a note for a task and a note for a subtask? I originally assumed that the note subtask id would be null which it apparently isn't. You should change your note adddate restriction to just retrieve the notes for the task. With the query written as it is the note retrieved for the task will be the last note added for the task or any subtasks of that task.


select t.dptid, t.priority, t.tskid, 0,
       t.tskname, t.priority, t.dlndate,
       t.estdate, t.pctcmpl,  
       n.notes, n.adddate  
from task t, notes n  
where t.tskid = n.tskid (+)  
AND n.adddate = (SELECT MAX(NOTES.ADDDATE) FROM NOTES WHERE   t.tskid = n.tskid)

union all  
 
select t.dptid, t.priority, t.tskid, s.subid,
       s.subname, s.priority, s.dlndate,
       s.estdate, s.pctcmpl,  
       n.notes, n.adddate
from task t, subtask s, notes n  
where t.tskid = s.tskid  
 and s.tskid = n.tskid (+)  
 and s.subid = n.subid (+)  
 
order by 1,2,3,4

0
 
johnc83Author Commented:
Just wanted to say thanks for your help. You got me going in the right direction for the sql command. Here is the finished sql command.

select task.tskid, task.tskname, task.priority, task.dptid,
TO_CHAR(task.dlndate, 'DY MM/DD/YY'),
TO_CHAR(task.estdate, 'DY MM/DD/YY'), task.pctcmpl,
notes.notes, notes.adddate, notes.tskid, notes.subid, 0, 0
from task, notes
where task.tskid = notes.tskid
and notes.subid is null
AND notes.adddate = (SELECT MAX(NOTES.ADDDATE) FROM NOTES
WHERE task.tskid = notes.tskid and notes.subid is null)
union all
select subtask.tskid, subtask.subname, task.priority, task.dptid,
TO_CHAR(subtask.dlndate, 'DY MM/DD/YY'),
TO_CHAR(subtask.estdate, 'DY MM/DD/YY'), subtask.pctcmpl,
notes.notes, notes.adddate, subtask.subid, notes.subid, task.tskid,
subtask.priority
from task, subtask, notes
where task.tskid = subtask.tskid
and subtask.subid = notes.subid
AND notes.adddate =
(SELECT MAX(NOTES.ADDDATE) FROM NOTES
WHERE subtask.subid = notes.subid and notes.subid is not null)
order by 4, 3, 1      
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now