dyarosh
asked on
Oracle SQL Help
I have a query that I have to build that I need help with. Here is the basics of the query (? is where I need the help).
SELECT ? AS ManagerName
FROM Employee
ManagerName needs to be pulled from one of two tables. If the ManagerID in the Employee Table is > 0, the Manager Name is retrieved using this query:
SELECT LastName || ', ' || FirstName FROM Employee WHERE EmployeeID = ManagerID
If the ManagerID in the Employee Table is < 0, the Manager Name is retrieved using this query:
SELELCT TeamName FROM OpenTeam WHERE OpenTeamID = ManagerID
If the ManagerID is 0, the Manager Name should be 'NONE'.
I don't know how to replace the ? with the queries to generate the ManagerName. Any help is greatly appreciated!
SELECT ? AS ManagerName
FROM Employee
ManagerName needs to be pulled from one of two tables. If the ManagerID in the Employee Table is > 0, the Manager Name is retrieved using this query:
SELECT LastName || ', ' || FirstName FROM Employee WHERE EmployeeID = ManagerID
If the ManagerID in the Employee Table is < 0, the Manager Name is retrieved using this query:
SELELCT TeamName FROM OpenTeam WHERE OpenTeamID = ManagerID
If the ManagerID is 0, the Manager Name should be 'NONE'.
I don't know how to replace the ? with the queries to generate the ManagerName. Any help is greatly appreciated!
ASKER
This is what I need my output to look like:
Employee Table
=============
EmployeeID, LastName, FirstName, ManagerSupervisorID
1, Doe, John, 2
2, Doe, Jane, 0
3, Smith, John, -1
OpenTeam Table
=============
OpenTeamID, TeamName
-1, OPEN (Smith, Jane)
Output
======
Doe, Jane
NONE
OPEN (Smith, Jane)
How do I create the query to get this output? (I kept the tables simple but the Employee Table has over 1400 records in it with many other fields. The OpenTeam table has about 20 records in it).
Employee Table
=============
EmployeeID, LastName, FirstName, ManagerSupervisorID
1, Doe, John, 2
2, Doe, Jane, 0
3, Smith, John, -1
OpenTeam Table
=============
OpenTeamID, TeamName
-1, OPEN (Smith, Jane)
Output
======
Doe, Jane
NONE
OPEN (Smith, Jane)
How do I create the query to get this output? (I kept the tables simple but the Employee Table has over 1400 records in it with many other fields. The OpenTeam table has about 20 records in it).
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you!!!!
FROM Employee
SELECT LastName || ', ' || FirstName FROM Employee WHERE EmployeeID = ManagerID AND ManagerID > 0
SELELCT o.TeamName FROM OpenTeam as o, Employee as e
WHERE o.OpenTeamID = e.ManagerID and e. ManagerID < 0
SELECT SUM(IF( ManagerID < 0, LastName , 'NONE)) as LName,
SUM(IF( ManagerID < 0, FirstName , 'NONE)) as FName,
FROM Employee WHERE EmployeeID = ManagerID