hi

i want to show each outer join with each records.

SQL> SELECT D.DEPTNO,E.ENAME
2 FROM EMP E,DEPT D
3 WHERE D.DEPTNO=E.DEPTNO(+);

DEPTNO ENAME
--------- ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD
40
50

17 rows selected.

But i want

DEPTNO ENAME
--------- ----------
10 CLARK
20 CLARK
30 CLARK
40 CLARK
50 CLARK
10 KING
20 KING
30 KING
40 KING
50 KING
10 MILLER
20 MILLER
30 MILLER
40 MILLER
50 MILLER

On 01.09.2006 15:04, aishwan@gmail.com wrote:
> hi
>
> i want to show each outer join with each records.
>
> SQL> SELECT D.DEPTNO,E.ENAME
> 2 FROM EMP E,DEPT D
> 3 WHERE D.DEPTNO=E.DEPTNO(+);
>
> DEPTNO ENAME
> --------- ----------
> 10 CLARK
> 10 KING
> 10 MILLER
> 20 SMITH
> 20 ADAMS
> 20 FORD
> 20 SCOTT
> 20 JONES
> 30 ALLEN
> 30 BLAKE
> 30 MARTIN
> 30 JAMES
> 30 TURNER
> 30 WARD
> 40
> 50
>
> 17 rows selected.
>
> But i want
>
> DEPTNO ENAME
> --------- ----------
> 10 CLARK
> 20 CLARK
> 30 CLARK
> 40 CLARK
> 50 CLARK
> 10 KING
> 20 KING
> 30 KING
> 40 KING
> 50 KING
> 10 MILLER
> 20 MILLER
> 30 MILLER
> 40 MILLER
> 50 MILLER
> .
> .
> .
> so on for each employ record with outer join
>
> please help it is not homework.

Where is the problem? AFAIK you just need to add an ORDER clause.

robert

<aishwan@gmail.com> a écrit dans le message de news: 1157115890.201755.35940@h48g2000cwc.googlegroups.c om...
| hi
|
| i want to show each outer join with each records.
|
| SQL> SELECT D.DEPTNO,E.ENAME
| 2 FROM EMP E,DEPT D
| 3 WHERE D.DEPTNO=E.DEPTNO(+);
|
| DEPTNO ENAME
| --------- ----------
| 10 CLARK
| 10 KING
| 10 MILLER
| 20 SMITH
| 20 ADAMS
| 20 FORD
| 20 SCOTT
| 20 JONES
| 30 ALLEN
| 30 BLAKE
| 30 MARTIN
| 30 JAMES
| 30 TURNER
| 30 WARD
| 40
| 50
|
| 17 rows selected.
|
| But i want
|
| DEPTNO ENAME
| --------- ----------
| 10 CLARK
| 20 CLARK
| 30 CLARK
| 40 CLARK
| 50 CLARK
| 10 KING
| 20 KING
| 30 KING
| 40 KING
| 50 KING
| 10 MILLER
| 20 MILLER
| 30 MILLER
| 40 MILLER
| 50 MILLER
| .
| .
| .
| so on for each employ record with outer join
|
| please help it is not homework.
|
| aishwan
|

You said this is not a homework but it seems to be one as this is meaningless.
Search for cartesian product in your book.

Regards
Michel Cadot

aishwan@gmail.com wrote:
> hi
>
> i want to show each outer join with each records.
>
> please help it is not homework.
>
> aishwan

Actually it is homework. The tables emp and dept are part of the scott
schema and your attempt to claim otherwise transparent. Please do not
ask us to do your school work for you.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org