Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple condition in select Statement

Author  Topic 

nsuresh316
Starting Member

2 Posts

Posted - 2013-12-11 : 14:05:36
How could I do this efficiently?I have a table with following data

Empname Team
Test1 MANAGER
Test2 TESTER
Test3 DEVELOPER
Test1 EMER RESPONSE
Test5 OPERATION
Test6 HOUSEKEEPING
Test7 MANAGER
Test5 EMER RESPONSE
Test9 OPERATION
Test10 EMER RESPONSE

I want to get the emp name and team name who is in more than 1 team
From our eg.

Empname Team
Test1 MANAGER
Test1 EMER RESPONSE
Test5 OPERATION
Test5 EMER RESPONSE

also I want to get the emp name based on the team names which i am mentioning

For eg if i need only Emp name who is a MANAGER and also in EMER RESPONSE team

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-12-11 : 14:54:21
Here's one way:


--emp name and team name who is in more than 1 team
select Empname, Team
from yourTable yt
where exists (select 1 from yourTable x where x.empname = yt.empname and x.team != yt.team)

--only Emp name who is a MANAGER and also in EMER RESPONSE team
select Empname
from yourTable yt
where exists (select 1 from yourTable x where x.empname = yt.empname and x.team != yt.team and x.Team = 'Manager')
and yt.Team = 'Emer Response'


Be One with the Optimizer
TG
Go to Top of Page

nsuresh316
Starting Member

2 Posts

Posted - 2013-12-11 : 23:31:36
Its works Thx
Go to Top of Page
   

- Advertisement -