oracle query

Sarma Vadlamani
Sarma Vadlamani used Ask the Experts™
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

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.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David VanZandtOracle Database Administrator III

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 DBA

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 Vadlamaniprogrammeranalyst


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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior Oracle DBA
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, 
       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

Sarma Vadlamaniprogrammeranalyst


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.

johnsoneSenior Oracle DBA
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 Vadlamaniprogrammeranalyst



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial