?
Solved

3 table join question

Posted on 2003-03-26
4
Medium Priority
?
239 Views
Last Modified: 2012-08-13
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
Comment
Question by:johnc83
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Expert Comment

by:Binary1
ID: 8213679
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
 

Author Comment

by:johnc83
ID: 8218607
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
 
LVL 4

Accepted Solution

by:
Binary1 earned 225 total points
ID: 8221419
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
 

Author Comment

by:johnc83
ID: 8248689
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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question