Can't use CONNECT BY and GROUP BY together

Question

I have two tables - employee and manager. One manager has many employees. I want to show it in parent-child relationship using CONNECT BY and GROUP BY command.

Structure for EMPLOYEE table:

CREATE TABLE employee (
  employee_id INTEGER,
  manager_id INTEGER,
  first_name VARCHAR2(10) NOT NULL,
  last_name VARCHAR2(10) NOT NULL
);

Structure for manager table:

CREATE TABLE manager(
  manager_id INTEGER NOT NULL,
  manager_dept VARCHAR2(20) NOT NULL,
  first_name VARCHAR2(30) NOT NULL,
  last_name  VARCHAR2(30) NOT NULL
);

I wrote this query:

SELECT E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, E.LAST_NAME, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE
FROM EMPLOYEE E
LEFT OUTER JOIN MANAGER M
ON E.MANAGER_ID=M.MANAGER_ID
connect by NOCYCLE m.manager_id=prior e.employee_id
GROUP BY manager_id;

It gives this error:

ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
Error at Line: 6 Column: 10

What's wrong with this group by clause?

You don't need `CONNECT BY` in this case, just `LEFT JOIN` is enough. Also there is no need of using `GROUP BY` because you are not calculating any aggregates. I suggest you to read documentation of that subjects to get an idea when you should use it. — Yaroslav Shabalin, Dec 16 '13 at 12:05

score 0 · Answer 1 · answered Dec 16 '13 at 11:59

0

You are getting column ambiguously defined because oracle is unable to determine which table's manager_id should be used for group by as you have not specified it. Correct Query is:

SELECT M.MANAGER_ID
FROM EMPLOYEE E
LEFT OUTER JOIN MANAGER M
ON E.MANAGER_ID=M.MANAGER_ID
connect by NOCYCLE m.manager_id=prior e.employee_id
GROUP BY m.manager_id;

answered Dec 16 '13 at 11:59

thiyaga

251
1
7

SELECT M.MANAGER_ID FROM EMPLOYEE E LEFT OUTER JOIN MANAGER M ON E.MANAGER_ID=M.MANAGER_ID CONNECT BY NOCYCLE m.manager_id=PRIOR e.employee_id GROUP BY e.employee_id; and what about this query – saurabhk Dec 16 '13 at 12:11
SELECT M.MANAGER_ID FROM EMPLOYEE E LEFT OUTER JOIN MANAGER M ON E.MANAGER_ID=M.MANAGER_ID CONNECT BY NOCYCLE m.manager_id=PRIOR e.employee_id where manager_id=10 GROUP BY m.manager_id – saurabhk Dec 16 '13 at 12:12
When using `groupby` you should have only those columns in your select. So if you group by employee id you select part should only have employee id an not manager id as you haven't used it in your group by. – thiyaga Dec 16 '13 at 12:14

score 0 · Answer 2 · answered Dec 16 '13 at 12:00

The column manager_id in your GROUP BY is ambiguous - it could be the manager_id of the employee table, as well as the manager_id of the manager table.

You also need to add the remaining columns to your GROUP BY. The complete query then becomes:

SELECT 
  E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, 
  E.LAST_NAME, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE
FROM EMPLOYEE E
LEFT OUTER JOIN MANAGER M
  ON E.MANAGER_ID=M.MANAGER_ID
connect by NOCYCLE m.manager_id=prior e.employee_id
GROUP BY 
  E.EMPLOYEE_ID, M.MANAGER_ID, E.FIRST_NAME, 
  E.LAST_NAME, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE;

Can't use CONNECT BY and GROUP BY together

2 Answers2