Data form different rows
-
I have the following table empdept(EmpID,DeptID)
Here are some data of the table
EmpID
DeptID
1 1 1 2 1 3 1 4 2 3 2 5 2 8 3 1 3 2 3 3 3 5 I need to show just the employees who are in the departments (1,2,3) the answer will be (1 & 3) .
or in (3,5) ----> (2,3)
or in (3,4,5,77,177) ---> ?????
if anyone can help to build such query ?
Thanks
-
set nocount on
go
declare @table table (EmpID INT, DEPTID INT)
insert into @table values (1, 1)
insert into @table values (1, 2)
insert into @table values (1, 3)
insert into @table values (1, 4)
insert into @table values (2, 3)
insert into @table values (2, 5)
insert into @table values (2, 8)
insert into @table values (3, 1)
insert into @table values (3, 2)
insert into @table values (3, 3)
insert into @table values (3, 5)
select empid
from @table
where deptid in (1, 2, 3)
group by empid
having count(1) = 3
empid
-----------
1
3
select empid
from @table
where deptid in (3, 5)
group by empid
having count(1) = 2
empid
-----------
2
3
Make the COUNT(1) value equal to the number of departments that you are comparing against. If the same employee value can be repeated for the same department-id (don't think so), then you can have a count(1) > ? operator.
-
HI,
Use this Query::
select distinct empid from emp where depid in(1,2)
Regards,
Amit Gupta
-
Not so simple as that I want it to be more general .
The departments may be passed by a parameter or to be in another table (they are unknown)
I put these figures to be understood.
-
Who are you responding to there, Tareg? It seems like it's Amit - in which case I agree. But rsharma's post seems to do what you need, no?...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thank you for your help
Here is the code as written by rsharma's
but with some modification since the empid and deptid are not unique in the table
---------------------------------
set nocount on
go
declare @tableValues table (empID int)
declare @count int
insert into @tableValues values (1)
insert into @tableValues values (2)
insert into @tableValues values (3)
insert into @tableValues values (6)
select @count =Count(*) from @tableValues
declare @table table (EmpID INT, DEPTID INT)
insert into @table values (1, 1)
insert into @table values (1, 2)
insert into @table values (1, 3)
insert into @table values (1, 4)
insert into @table values (2, 3)
insert into @table values (2, 5)
insert into @table values (2, 3)
insert into @table values (2, 3)
insert into @table values (2, 8)
insert into @table values (3, 1)
insert into @table values (3, 2)
insert into @table values (3, 3)
insert into @table values (3, 5)
insert into @table values (3, 3)
insert into @table values (3, 3)
insert into @table values (3, 3)
insert into @table values (3, 6)
select Empid from (
select empid,DEPTID
from @table
where deptid in (select Empid from @tableValues)
group by empid,DEPTID ) as sss
group by Empid having count(*) = @count
--------------------------------------
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply