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

 
johnc83Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.