CODEWITHSUNDEEP
sqloracle-databasejoin
Enferno
Enferno

Reputation: 93

SQL query to display a parent table's record followed by all its child table's record

I need to write down a single SQL statement that displays the following output by using tables EMP and DEPT.

Output:

DEPTNO DNAME            LOC           
---------- -------------- ------------- 
    10 ACCOUNTING     NEW YORK      

 EMPNO ENAME
---------- ----------
  7782 CLARK
  7839 KING
  7934 MILLER


    20 RESEARCH       DALLAS        
 EMPNO ENAME
---------- ----------
  7369 SMITH
  7566 JONES
  7788 SCOTT
  7876 ADAMS
  7902 FORD


    30 SALES          CHICAGO       
 EMPNO ENAME
---------- ----------
  7499 ALLEN
  7521 WARD
  7654 MARTIN
  7698 BLAKE
  7844 TURNER
  7900 JAMES

6 rows selected.

    40 OPERATIONS     BOSTON        


no rows selected

So I need this specific output from a query where the dept information is shown first then all its respective records from the child table.

Upvotes: 1

Views: 151

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

From my point of view, that task isn't as simple as it looks. OK, my SQL*Plus capabilities probably aren't as good as they used to / should be. The main problem is how to make EMP table's headings repeat for each department (and no, I don't know how to do that).

The closest result I managed to produce is this:

SQL> col deptno format 999999
SQL> col dname  format a10
SQL> col loc    format a10
SQL> set linesize 30
SQL> set recsep off
SQL> break on deptno on dname on loc
SQL>
SQL> select d.deptno, d.dname, d.loc, e.empno, e.ename
  2  from dept d left join emp e on e.deptno = d.deptno
  3  order by d.deptno;

 DEPTNO DNAME      LOC
------- ---------- ----------
     EMPNO ENAME
---------- ----------
     10 ACCOUNTING NEW YORK
      7839 KING

      7782 CLARK

      7934 MILLER
     20 RESEARCH   DALLAS
      7902 FORD

      7369 SMITH

      7566 JONES
     30 SALES      CHICAGO
      7900 JAMES

      7844 TURNER

      7654 MARTIN

      7521 WARD

      7499 ALLEN

      7698 BLAKE
     40 OPERATIONS BOSTON


13 rows selected.

Once you, Enferno (or anyone else) manages to get exactly what's been asked for, I'd really like to see the final code.

Upvotes: 1

Related Questions