CODEWITHSUNDEEP
sql
user466334
user466334

Reputation: 65

Should I Use a Self-Join

If I have a table...

  ID     Name   Manager 
  0      Joe    Sue  
  1      Jake   Tom
  0      Joe    Tom
  2      Larry  Red
  2      Larry  Paul
  1      Jake   Paul

I want the output to be....

 ID   Name   Manager1    Manager2
 0    Joe    Sue         Tom
 1    Jake   Tom         Paul
 2    Larry  Red         Paul

Thanks...

Upvotes: 2

Views: 206

Answers (4)

pClear
pClear

Reputation: 3

If I am using MySql for answered. I am agree to the Andy's answer, but in a little revision, like this code:

-- DDL Schema SQL
CREATE TABLE Managers  (
  `ID` INTEGER,
  `Name` VARCHAR(5),
  `Manager` VARCHAR(4)
);

INSERT INTO Managers 
  (`ID`, `Name`, `Manager`)
VALUES
  ('0', 'Joe', 'Sue'),
  ('1', 'Jake', 'Tom'),
  ('0', 'Joe', 'Tom'),
  ('2', 'Larry', 'Red'),
  ('2', 'Larry', 'Paul'),
  ('1', 'Jake', 'Paul');
-- DQL Query SQL
SELECT DISTINCT 
    F.ID, F.Name, S.Manager, F.Manager
FROM 
    (SELECT 
         ID, Name, MIN(manager) manager 
     FROM Managers  
     GROUP BY ID, Name) F,
    (SELECT
         ID, Name, MAX(manager) manager 
     FROM Managers  
     GROUP BY ID, Name) S
WHERE 
    F.ID = S.ID
-- The others SQL Database manager maybe?
    -- AND S.Manager <> F.Manager
    -- AND F.ID < S.ID

If you want to see the MySQL database demo, you click this link on DB-Fiddle.com.

My inspiration:

Upvotes: 0

Andy
Andy

Reputation: 2318

If your keeping the tables a join would be best.

If you hate joins, you could combine the max and min managers, and even then it would work if there is always 2 managers and they can't have the same name.

The below should work if I remember how to join up 2 queries correctly. but i would advise to see if it is possible to rework your table, have a separate table linking people to each other in a manager employee relation.

SELECT DISTINCT 
    F.ID, F.Name, S.Manager, F.Manager
FROM 
    (SELECT 
         ID, Name, MIN(manager) manager 
     FROM Managers 
     GROUP BY ID, Name) F,
    (SELECT
         ID, Name, MAX(manager) manager 
     FROM Managers 
     GROUP BY ID, Name) S
WHERE 
    F.ID = S.ID
    AND S.Manager <> F.Manager
    AND F.ID < S.ID

Upvotes: 0

squirrel
squirrel

Reputation: 171

If I have understood your request properly, yes, something like would produce the results you are looking for.

SELECT
t1.Name Name,
t1.Manager Manager1,
t2.Manager Manager2
FROM
Table t1
inner join Table t2 on t1.Manager = t2.Name

Of course a foreign key back to the index column would be preferential to strong comparisons for performance.

Upvotes: 4

Rich
Rich

Reputation: 3821

Yeah, if your table was called 'Managers':

SELECT Mgr1.ID,Mgr1.Name,Mgr1.Manager,Mgr2.Manager
FROM Managers AS Mgr1
LEFT JOIN Managers AS Mgr2
ON Mgr1.ID=Mgr2.ID

Upvotes: 3

Related Questions