Saturday, September 12, 2009

For every record in the master table i want the records from the detail table

select nvl2( empno, null, deptno ) deptno,
2 nvl2( empno, null, dname ) dname,
3 empno, ename, sal
4 from (
5 select d.deptno deptno, d.dname, e.empno empno, e.ename, e.sal
6 from dept d, emp e
7 where d.deptno = e.deptno
8 union all
9 select d.deptno, d.dname, null, null, null
10 from dept d
11 order by deptno, empno NULLS FIRST
12 ) x
13 /

DEPTNO DNAME EMPNO ENAME SAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
20 RESEARCH
7369 SMITH 800
7566 JONES 2975
7788 SCOTT 3000
7876 ADAMS 1100
7902 FORD 3000
30 SALES
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
40 OPERATIONS

18 rows selected.

No comments: