Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

HELP! Self join: 2 columns compare 2 columns

j9678j9678 Member Posts: 16
Given an example
Staff table:
StaffID StaffName DeptID ManagerID
100 Apple 1 100
101 Orange 1 100
102 Lemon 2 102
103 Pinapple 2 102
104 Wrong 1 102

Each Dept have one manager. E.g. staff Apple is the manager of Dept 1
Orange is one of the staff of Dept 1

Need to write a SQL statement to list all staff ID and name if
he/she does not belong to the same department as his/her manager.

That mean need to find out the staffname "Wrong"

Anybody have a clue ?

Comments

  • HackmanCHackmanC Member Posts: 441
    I hope it help.

    I can't test on Access less than 2007,
    but sure works in 2007, MSSQL & MSDE.

    [code]CREATE TABLE Test (
    StaffID int,
    StaffName varchar(10),
    DeptID int,
    ManagerID int
    );

    INSERT INTO Test (StaffID, StaffName, DeptID, ManagerID) VALUES (100, 'Admin', 10, null);
    INSERT INTO Test (StaffID, StaffName, DeptID, ManagerID) VALUES (101, 'anthony', 10, 100);
    INSERT INTO Test (StaffID, StaffName, DeptID, ManagerID) VALUES (102, 'paulus', 11, 100);

    SELECT * FROM Test;

    [color=Blue]"StaffID","StaffName","DeptID","ManagerID"
    100, "Admin", 10, null
    101, "anthony", 10, 100
    102, "paulus", 11, 100
    [/color]
    SELECT
    a.*,
    (SELECT TOP 1 StaffName FROM Test b WHERE a.ManagerID = b.StaffID) AS ManagerName,
    (SELECT TOP 1 DeptID FROM Test b WHERE a.ManagerID = b.StaffID) = a.DeptID AS SameDeptID
    FROM
    Test a
    -- WHERE: YOU KNOW WHAT

    [color=Blue]"StaffID","StaffName","DeptID","ManagerID", "ManagerName","SameDeptID"
    100, "Admin", 10, null, null, null
    101, "anthony", 10, 100, "Admin", 1
    102, "paulus", 11, 100, "Admin", 0
    [/color]
    DROP TABLE Test[/code]

    [red]Good luck![/red]
    [blue]Hackman[/blue]
  • j9678j9678 Member Posts: 16
    : I hope it help.
    :
    : I can't test on Access less than 2007,
    : but sure works in 2007, MSSQL & MSDE.
    :
    : [code]: CREATE TABLE Test (
    : StaffID int,
    : StaffName varchar(10),
    : DeptID int,
    : ManagerID int
    : );
    :
    : INSERT INTO Test (StaffID, StaffName, DeptID, ManagerID) VALUES (100, 'Admin', 10, null);
    : INSERT INTO Test (StaffID, StaffName, DeptID, ManagerID) VALUES (101, 'anthony', 10, 100);
    : INSERT INTO Test (StaffID, StaffName, DeptID, ManagerID) VALUES (102, 'paulus', 11, 100);
    :
    : SELECT * FROM Test;
    :
    : [color=Blue]"StaffID","StaffName","DeptID","ManagerID"
    : 100, "Admin", 10, null
    : 101, "anthony", 10, 100
    : 102, "paulus", 11, 100
    : [/color]
    : SELECT
    : a.*,
    : (SELECT TOP 1 StaffName FROM Test b WHERE a.ManagerID = b.StaffID) AS ManagerName,
    : (SELECT TOP 1 DeptID FROM Test b WHERE a.ManagerID = b.StaffID) = a.DeptID AS SameDeptID
    : FROM
    : Test a
    : -- WHERE: YOU KNOW WHAT
    :
    : [color=Blue]"StaffID","StaffName","DeptID","ManagerID", "ManagerName","SameDeptID"
    : 100, "Admin", 10, null, null, null
    : 101, "anthony", 10, 100, "Admin", 1
    : 102, "paulus", 11, 100, "Admin", 0
    : [/color]
    : DROP TABLE Test[/code]:
    :
    : [red]Good luck![/red]
    : [blue]Hackman[/blue]

    Thanks alot, It works!

    select s1.staffid, s1.staffname
    from staff s1
    where (select staffname from staff s2
    where s1.managerid=s2.staffid)
    and not
    s1.deptid=(select deptid from staff s2
    where s1.managerid = s2.staffid)
Sign In or Register to comment.