SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Multiple condition in select Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nsuresh316
Starting Member

2 Posts

Posted - 12/11/2013 :  14:05:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5944 Posts

Posted - 12/11/2013 :  14:54:21  Show Profile  Reply with Quote
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 - 12/11/2013 :  23:31:36  Show Profile  Reply with Quote
Its works Thx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000