Sybase query self-join-CodePudding

I have the following table storing ID and the department each ID works for. (One ID can work for multiple departments. I would like to find the IDs that work only for one department.

create table tempdb..dept (ID int, Dept varchar(10))

go

insert into tempdb..dept (ID, Dept) values (1, "HR")

insert into tempdb..dept (ID, Dept) values (2, "HR")

insert into tempdb..dept (ID, Dept) values (3, "HR")

insert into tempdb..dept (ID, Dept) values (4, "HR")

insert into tempdb..dept (ID, Dept) values (1, "Finance")

insert into tempdb..dept (ID, Dept) values (5, "Finance")

insert into tempdb..dept (ID, Dept) values (6, "Finance")

insert into tempdb..dept (ID, Dept) values (4, "Finance")

insert into tempdb..dept (ID, Dept) values (1, "IT")

insert into tempdb..dept (ID, Dept) values (7, "IT")

insert into tempdb..dept (ID, Dept) values (8, "IT")

insert into tempdb..dept (ID, Dept) values (4, "IT")

Desired output:

ID   Dept
2    HR
3    HR
5    Finance
6    Finance
7    IT
7    IT

CodePudding user response：

You can try HAVING count get non-duplicate ID from table then do use self-join

SELECT t2.*
FROM (
    SELECT ID
    FROM tempdb..dept
    GROUP BY ID
    HAVING COUNT(*) = 1
) t1 INNER JOIN tempdb..dept t2
ON t1.ID = t2.ID

another way you can try to use correlated subquery

SELECT t2.*
FROM tempdb..dept t2
ON t1.ID = t2.ID
WHERE EXISTS (
    SELECT 1
    FROM tempdb..dept tt
    WHERE t2.ID = tt.ID
    GROUP BY tt.ID
    HAVING COUNT(*) = 1
)