CODEWITHSUNDEEP
sqloracle-databaseselect
Ryan Grainger
Ryan Grainger

Reputation: 21

How to join two SELECT statements operating on the same column on a single table with different conditions?

I am trying complete the following exercise

Create a query that has the employee name, supervisor name, and employee salary for each employee who works in the 'Research' department

Which operates on the following schema:

https://scontent-lht6-1.xx.fbcdn.net/v/t34.0-12/23140228_10214571052854224_1441212020_n.png?oh=15f361ec2679373f2cfeb427dd5b6978&oe=59F9593B

I have developed the following query which works somewhat, but does not match the rows accordingly so I end up with a result consisting of the employee names and salaries, with separate rows for the manager names. My query is as follows:

CREATE VIEW research AS
SELECT NULL as Fname, NULL as Lname, NULL as Salary, Lname as Manager from EMPLOYEE
WHERE Ssn IN (SELECT Superssn from EMPLOYEE WHERE Dno = (SELECT Dnumber from DEPARTMENT WHERE Dname = 'Research'))
UNION
SELECT Fname, Lname, Salary, Null as Manager  FROM EMPLOYEE
WHERE Dno = (SELECT Dnumber from DEPARTMENT WHERE Dname = 'Research');

Can anyone see what is wrong with this? Am I going the right way about it by using the UNION command?

Thank you.

Upvotes: 0

Views: 96

Answers (2)

kfinity
kfinity

Reputation: 9091

No, you don't want UNION for this.

Why do you have super_ssn on EMPLOYEE and also mgr_ssn on DEPARTMENT? Isn't that redundant?

Anyway, here's another way to do it.

select fname, lname, salary, 
    (select lname from employee e where e.ssn = employee.superssn) manager
from employee
where dno = (SELECT Dnumber from DEPARTMENT WHERE Dname = 'Research');

Upvotes: 1

Todd Peek
Todd Peek

Reputation: 41

It appears this is what you're trying to accomplish.

select
    emp.FName       as EmployeeFName
    ,emp.LName      as EmployeeLName
    ,sup.FName      as SupervisorFName
    ,sup.LName      as SupervisorLName
    ,emp.Salary     as Salary

from
    Employee emp
    inner join  Department dept
            on  emp.dno = dept.dnumber
    inner join  Employee sup
            on  emp.SuperSSN = sup.SSN

where
    dept.Name = 'Research'

Upvotes: 3

Related Questions