Sql table output

Question

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.

Do a primary select of ``ManID`` and then every ``EmpId`` that matches that ``ManID`` — Noam Rathaus, Dec 04 '13 at 15:51
There are lots of ways to approach this, but for valid testing, I think you'd need to clean up your data. You've got people (characters?) reporting to each other. Daisy reports to Scrooge, and Scrooge reports to Daisy. — Andrew, Dec 04 '13 at 16:17

Noam Rathaus · Answer 1 · 2013-12-04T23:16:44.867

1

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

edited Dec 04 '13 at 23:16

answered Dec 04 '13 at 15:54

Noam Rathaus

5,405
2
28
37

This works except it alters the employees names to the managers names unfortunately – WhiteSpider Dec 04 '13 at 16:33
I believe I fixed the bug – Noam Rathaus Dec 04 '13 at 23:16
1

Why not use inner join syntax? – Chris Farmer Dec 28 '13 at 15:32
@ChrisFarmer explain? – Noam Rathaus Dec 28 '13 at 19:10

Sql table output

1 Answers1

Linked