Re: help on a simple query

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 - 15:16:22 CST