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
– Noam Rathaus, Commented Dec 4, 2013 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
– Andrew, Commented Dec 4, 2013 at 16:17

Noam Rathaus · Accepted Answer · 2013-12-04 23:16:44Z

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 4, 2013 at 23:16

answered Dec 4, 2013 at 15:54

Noam Rathaus

5,6082 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

WhiteSpider Over a year ago

This works except it alters the employees names to the managers names unfortunately

Noam Rathaus Over a year ago

I believe I fixed the bug

Chris Farmer Over a year ago

Why not use inner join syntax?

Noam Rathaus Over a year ago

@ChrisFarmer explain?

Collectives™ on Stack Overflow

Sql table output

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related