CODEWITHSUNDEEP
sqlsql-serverjoin
WhiteSpider
WhiteSpider

Reputation: 405

Sql table output

I've been given two tables: Employee and Staff

|ID| Name  |Surname|Postion|           |EmpID|ManID|
----------------------------           ------------- 
|1 |Scrooge|McDuck |Manager|           |3    |1    |
|2 |Daisy  |Duck   |Manager|           |7    |1    |
|3 |Donald |Duck   |Support|           |6    |2    | 
|4 |Minny  |Mouse  |Support|           |4    |2    |
|5 |Mickey |Mouse  |Support|           |2    |1    |
|6 |Goofy  |       |Support|           |1    |2    |
|7 |Pluto  |       |Support|           |5    |2    |
|8 |Huey   |Duck   |Support|
|9 |Dewey  |Duck   |Support|
|10|Louie  |Duck   |Support|

I am asked for a sql statement that will produce following output 

| Name  |Surname|Postion|Manager Name|Manager Positon|          
------------------------            
|Donald |Duck   |Support|Scrooge     |Manager         
|Pluto  |       |Support|Scrooge     |Manager         
|Goofy  |       |Support|Daisy       |Manager        
|Minny  |Mouse  |Support|Daisy       |Manager       
|Daisy  |Duck   |Support|Scrooge     |Manager        
|Scrooge|McDuck |Manager|Daisy       |Manager        
|Mickey |Mouse  |Manager|Daisy       |Manager

So far I've created a view which displays everything but doesn't create the two new columns and doesn't give the names of the managers. 

CREATE VIEW example
AS
    SELECT * FROM Employee
    JOIN StaffLink
    ON Employee.ID = StaffLink.EmpID

    SELECT 
GO

Please guide me.

Upvotes: 0

Views: 560

Answers (1)

Noam Rathaus
Noam Rathaus

Reputation: 5598

SQL:

SELECT Staff.EmpID, Manager.Name AS `Manager Name`, 
       Manager.Position AS `Manager Position`, 
       Employee.Name, Employee.Surname, Employee.Position
FROM (Staff, Employee AS Manager, Employee)
WHERE Manager.ID = Staff.ManID AND
      Employee.ID = Staff.EmpID;

I believe it is now fixed

Upvotes: 1

Related Questions