Thank you, it make sense.
"Wendy Smiley" <wendy01_at_charter.net> wrote in message
news:t79351t397t63_at_corp.supernews.com...
> You are getting a Cartesion Join between the dept2 table and employees
> because you don't have dept2 in your where clause. Try this instead:
>
> SELECT x.ename, y.ename, z.dname, nvl(x.sal, 0) + nvl(x.comm, 0) "total
> salary", x.hiredate
> FROM bbitzer.emp2 x, bbitzer.emp2 y, dept2 z
> WHERE x.mgr = y.empno and x.deptno = y.deptno and x.job = 'clerk'
> and z.deptno = x.deptno;
>
> Mike.
>
> "albrrat" <albrrat_at_hotmail.com> wrote in message
> news:980712999.98607_at_news2.bigplanet.com...
> > a query to display all employees name, managers name department name,
total
> > salary(sal+comm) and hire date whose job title = 'clerk'
> >
> > what I have:
> > select x.ename, y.mgr,dname, x.sal, x.comm, x.hiredate
> > FROM bbitzer.emp2 x, bbitzer.emp2 y, dept2 z
> > where x.mgr = y.empno and x.deptno = y.deptno and x.job = 'clerk';
> >
> > It prints:
> > ENAME MGR DNAME SAL COMM HIREDATE
> > ---------- ---------- -------------- ---------- ---------- ---------
> > james 7839 accounting 950 03-DEC-81
> > james 7839 research 950 03-DEC-81
> > james 7839 sales 950 03-DEC-81
> > james 7839 operations 950 03-DEC-81
> > miller 7839 accounting 1300 23-JAN-82
> > miller 7839 research 1300 23-JAN-82
> > miller 7839 sales 1300 23-JAN-82
> > miller 7839 operations 1300 23-JAN-82
> >
> > 8 rows selected
> >
> > tables:
> >
> > SQL> CREATE TABLE dept2
> > 2 (
> > 3 deptno number(2) NOT NULL,
> > 4 dname char(14),
> > 5 loc char(13)
> > 6 );
> > SQL> CREATE TABLE emp2
> > 2 (
> > 3 empno number(4) NOT NULL,
> > 4 ename char(10),
> > 5 job char(9),
> > 6 mgr number(4),
> > 7 hiredate date,
> > 8 sal number(7,2),
> > 9 comm number(7,2),
> > 10 deptno number(2) NOT NULL
> > 11 );
> >
> > SQL>ALTER TABLE dept2 ADD(CONSTRAINT pk_dept PRIMARY KEY(deptno));
> > Table altered.
> >
> > SQL> ALTER TABLE EMP2 ADD(CONSTRAINT pk_emp2 PRIMARY KEY(empno));
> >
> > Table altered.
> >
> > SQL> ALTER TABLE EMP2 ADD(CONSTRAINT fk_emp2 FOREIGN KEY(deptno)
references
> > dept2(deptno));
> >
> > Table altered.
> >
> > SQL> ALTER TABLE EMP2 ADD(CONSTRAINT fk2_emp2 FOREIGN KEY(MGR)
REFERENCES
> > EMP2(EMPNO));
> >
> > Table altered.
> >
> >
>
>
Received on Sun Jan 28 2001 - 17:04:57 CST
