"column ambiguously defined"

Question

So I'm totally having a hard time on comprehending SQL this semester. I'm really not confident on the knowledge I have in SQL and I'm trying to work on this exercise where it says:

A SELECT statement to retrieve a list of employees with the columns DEPARTMENT_ID , DEPARTMENT_NAME , FULL_NAME, JOB_TITLE where FULL_NAME is the First name and Last name concatenated with a space between them for those employees that their Job title contains the word 'Sales'. The list must be sorted by job title and department name.

So far, I came up with this

SELECT department_id, 
    department_name, 
    first_name || ' ' || last_name as Full_name, 
    job_title
FROM departments d, employees e, jobs j
WHERE d.department_id=e.department_id 
HAVING job_title LIKE '%Sales%';

and the error says:

Error starting at line 1 in command:
select department_id, department_name, first_name || ' ' || last_name as Full_name, job_title
from departments d, employees e, jobs j
where d.department_id=e.department_id 
having job_title like '%Sales%'
Error at Command Line:1 Column:8
Error report:
SQL Error: ORA-00918: column ambiguously defined
00918. 00000 -  "column ambiguously defined"
*Cause:    
*Action:

Any tips and help will do.

And change `HAVING` to `AND`; `HAVING` is like `WHERE`, but for aggregate expressions, like SUM()s and COUNT()s. — Adam Musch, Mar 07 '13 at 20:55

score 6 · Answer 1 · answered Mar 07 '13 at 20:20

6

"Column 8" in your command is the eighth character, the department_id field. You're SELECTing the column department_id from two tables which both have that column; it's ambiguous because the parser sees two possible values for department_id (the one from departments and the one from employees), even though we know based on your WHERE clause that those values will be identical. You have to pick one:

select d.department_id, ...

answered Mar 07 '13 at 20:20

Jim Dagg

2,044
22
29

thanks that really helped me understand so what i did was this select departments.department_id, employees.department_id, departments.department_name, employees.first_name || ' ' || employees.last_name as full_name, job_title from departments, employees, jobs where job_title like '%Sales%'; now, the thing is that it outputs like at least 5000 rows i must be missing something – Mar 07 '13 at 20:50
1

@user2145903 You took out "where d.department_id=e.department_id" so now it's pulling all the rows. – Slapout Mar 07 '13 at 21:08
ok thanks so what i did was implemented where employees.department_id=departments.department_id and job_title like '%Sales%' order by job_title, full_name asc; it narrowed it down into 212 rows but the problem now is the reason why its 212 is because its duplicated it should be less than 212 rows. is there any other statement i could apply to remove duplication? – Mar 07 '13 at 21:37
i also tried using DISTINCT and still shows me 212rows – Mar 07 '13 at 21:43
1

In the SQL still above, you haven't got a join to the jobs table, so it's probably duplicating entries for every job. – AjV Jsy Mar 07 '13 at 23:32

score 3 · Answer 2 · answered Mar 07 '13 at 20:17

3

Specify whether it's e.department_id or d.department_id (etc) for the selected fields

answered Mar 07 '13 at 20:17

AjV Jsy

5,799
4
34
30

"column ambiguously defined"

2 Answers2