CODEWITHSUNDEEP
sqloracle-database
rjmcb
rjmcb

Reputation: 3745

How to add subquery as a column in SQL

How do I add a subquery as a column in my SQL script?

e.g.

Select emp_no, name,gender ,
    (select department_name from departments where employees.emp_no = departments.emp_no)
from employees

PS: I'm using oracle 8

Upvotes: 3

Views: 27910

Answers (7)

Newbie
Newbie

Reputation: 63

left join is the best-practice, and should be faster in performance:

Select e.emp_no, e.name, e.gender , d.department_name
from employees e left join departments d on e.emp_no = d.emp_no;

Upvotes: 1

CompEng
CompEng

Reputation: 7376

can you try this:

SELECT em.emp_no, em."name",em.gender , (SELECTdistinctdp.department_name FROM departments dp WHERE em.emp_no = dp.emp_no) my_sub FROM employees em

Upvotes: 0

user3097929
user3097929

Reputation: 1

This query is your answer but it will work only if there is one column mentioned in that if we use more than one column than it will retrun an error .

"Select employee_id,first_name,
    (select department_name,manager_id from departments where employees.department_id = departments.department_id) as new_column
from employees;"

Upvotes: 0

JBC
JBC

Reputation: 112

That looks reasonably sound, I would suggest some (possible typos) cleaning up: add a comma after "gender" and declare the table names, also set the subquery alias

Select employees.emp_no, employees.name, employees.gender,  
    (select departments.department_name from departments where employees.emp_no = departments.emp_no) as dept_name
from employees

Alternatively, a nice join would would work too, if the other data is feasible:

Select employees.emp_no, employees.name, employees.gender,  departments.department_name
from employees
inner join departments on employees.emp_no = departments.emp_no

Upvotes: 1

Aditya
Aditya

Reputation: 1723

Going by the semantics, what I understand is that you want an employee's department name to be shown alongside his/her other information. I would suggest you do a join instead:

Select emp_no, name, gender, department_name
from employees emp, departments dept
where emp.emp_no = dept.emp_no;

Upvotes: 1

SS781
SS781

Reputation: 2649

The below is what you need. Just added a comma after gender. This subquery would need to return only one row for each result as well or else an error will be seen.

Select emp_no, name,gender, 
    (select department_name from departments where employees.emp_no = departments.emp_no)
from employees

Upvotes: 0

user353gre3
user353gre3

Reputation: 2755

You seem to be missing comma after gender. 

Select emp_no, name,gender ,
(select department_name from departments     where employees.emp_no = departments.emp_no) as dept_name from employees

Upvotes: 0

Related Questions