Re: help on a simple query

Change x.deptno = y.deptno in
x.deptno = z.deptno

Seems you've hit a reason to use more meaningful aliases.

Hth,

Sybrand Bakker, Oracle DBA

"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:03:46 CST