0

I have been trying to select all columns from one table into a single column. The problem is I can't seem to get the foreign key "department_id" to show up. If I run this code:

select employee_id || ',' || ' ' || last_name || ',' || ' ' || job_id || ',' || ' ' ||TO_CHAR(hire_date, 'DD Mon YY' ) || ',' || ' '|| department_id
AS "The_Output"
FROM employee;

The information from the "department_id" simply doesn't show. While this code:

select employee_id || ',' || ' ' || last_name || ',' || ' ' || job_id || ',' || ' ' ||TO_CHAR(hire_date, 'DD Mon YY' ) || ',' || ' '|| department_id
AS "The_Output"
FROM employee, department;

gives me this error:

ORA-00918: column ambiguously defined

I have tried to UNION them but that didn't work

Tuonelan Joutsen
  • 23
  • 5
  • Possible duplicate of [ORA-00918: column ambiguously defined in SELECT \*](http://stackoverflow.com/questions/6233086/ora-00918-column-ambiguously-defined-in-select) – user2672165 Oct 25 '15 at 12:15

1 Answers1

2

That is because department_id exists in both employee and department table. So use alias while selecting.

Also your query is missing the join condition, so you will get cartesian product from both tables. Use something like below.

SELECT E.EMPLOYEE_ID || ',' || ' ' || E.LAST_NAME || ',' || ' ' || E.JOB_ID || ',' || ' ' ||TO_CHAR(E.HIRE_DATE, 'DD Mon YY' ) || ',' || ' '|| E.DEPARTMENT_ID
AS "The_Output"
FROM EMPLOYEE E INNER JOIN DEPARTMENT D
ON E.DEPARTMENT_ID=D.DEPARTMENT_ID
Utsav
  • 7,914
  • 2
  • 17
  • 38
  • I have tried this: `SELECT employee.employee_id || ',' || ' ' || employee.last_name || ',' || ' ' || employee.job_id || ',' || ' ' ||to char(HIRE_DATE, 'DD Mon YY' ) || ',' || ' '|| employee.department_id AS "The_Output" FROM employee INNER JOIN department_id ON employee.department_id=department.department_id;` it gives me a missing expression error – Tuonelan Joutsen Oct 25 '15 at 12:14
  • Ok I see it, you are joining `employee INNER JOIN department_id`. Should be `department`. Try my query exactly. – Utsav Oct 25 '15 at 12:19
  • It says > no data found. I have also changed department_id in the INNER JOIN into department, it gives me the messing expression error. – Tuonelan Joutsen Oct 25 '15 at 12:21
  • 1
    :) That is because either one of your table is empty or there are no matching department_id – Utsav Oct 25 '15 at 12:25
  • I just viewed department_id in both tables. While it is full in the department table where it is a primary key, it is empty in the employee where it is a foreign key. Shall I manually insert the same data into the employee table's department_id? – Tuonelan Joutsen Oct 25 '15 at 12:30
  • Sure. Also try sqlfiddle.com to test your scenarios so other can view it too. – Utsav Oct 25 '15 at 13:12
  • As your original question was for column ambiguity, which is resolved now, So please accept the answer by clicking the tick symbol on left of the answer so it can be closed. – Utsav Oct 25 '15 at 13:13