oracle query

Dept table
deptno     dname        
10            abc
20           bbb
30          ccc
40          ddd

term table
termid termstartdate  status      deptno              
1          2000/01/01    active       10
2          2001/01/01     active         20
3          2002/01/01    active       30
4          2003/01/01     complete 40



emp table
  empno deptno emptype  
101        10           manager
102         20          clerk


 casework table

casework
caseid  deptno      casedetials
1001     10              details1
1002     30              details2

query to return result  as below


deptno   dname   termid termstartdate  status      deptno      empno deptno emptype    ceid  dno    asedls  
10           abc        1          2000/01/01         active       10            101         10     manager         1001  10       det1
20          bbb         2          2001/01/01        activ        20           102       20       clerk                 null     null        null
30          ccc          3          2002/01/01          active     30          null         null      null              1002    30    det2
40          ddd         4          2003/01/01       complete  40         null          null    null            null     null   null

how can i achieve this result i use this query in oracle reqports.
thanks
Sarma VadlamaniprogrammeranalystAsked:
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.

David VanZandtOracle Database Administrator IIICommented:
Speaking for myself, I'm here to help others learn and work through problems -- but not to do their work, or classwork, for them.  Unless of course there are contracting fees on the table.  If you have sample code to attach, showing your work so far, I would be glad to do my best for you.
johnsoneSenior Oracle DBACommented:
Looks like a fairly simple query.  What aren't you telling us?  What do you have so far?  What isn't working about it?

I hate to say it, but this question is structured like many questions that go on for 20 or more posts before we get to the real question.

The first set of issues I see are what is the driving table, what are you trying to solve?

It should be a simple join between DEPT and TERM and then outer joins to the other 2 tables, but I'm afraid what you need is more complicated than that and you aren't explaining it correctly.
Sarma VadlamaniprogrammeranalystAuthor Commented:
the driving table is dept and all records from term should come if there is other table data then it should come or just show null values
i tried with union but the result is not what i am expecting.
how can I use more than one outer join in oracle as it will not support.
thanks.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

johnsoneSenior Oracle DBACommented:
Since when can you not do multiple outer joins in a single query?  You have always been able to do that.

I removed all the duplication of department number.  Don't see why you need the same column in the output 4 times.
SELECT a.deptno, 
       a.dname, 
       b.termid, 
       b.termstartdate, 
       b.status, 
       c.empno, 
       c.emptype, 
       d.caseid      ceid, 
       d.casedetails asedls 
FROM   dept a 
       join term b 
         ON a.deptno = b.deptno 
       left outer join emp c 
                    ON a.deptno = c.deptno 
       left outer join casework d 
                    ON a.deptno = d.deptno; 

Open in new window

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
Sarma VadlamaniprogrammeranalystAuthor Commented:
thanks for the response but this is not solving my proble,

i think the above query only does the left outer join but my requirement is
driving column is deptno
and one deptno can have multiple terms and each term can have multiple employees and multiple cases.
what i want is for one dept number the active status term along with all employee types and the only the latest case details

for eg. dept 10 can have 3 terms with each starting on different dates - for this I am using term start date

and each term may have 3 or 4 employee types information starting on different dates for this iam using employee type change date. so all employee types should come

and each term may have 3 or 4 case reviews but require only the latest casereveiws. for this I am using case review date.


thanks.
johnsoneSenior Oracle DBACommented:
There are no restrictions in the query so all rows will be returned.  The query that I posted will give the output that you posted.  If there is something that the query is not doing, please provide sample data and expected results that show it.
Sarma VadlamaniprogrammeranalystAuthor Commented:
thanks
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
Oracle Database

From novice to tech pro — start learning today.