Fairly simple subtraction issue

Question

Post reply

Fairly simple subtraction issue

vick.ram79

SSCommitted

Points: 1637
More actions
April 18, 2009 at 10:13 am

#155581

Here is my table:
Create Table #Bar (
EmployeeID int
, EName varchar(50)
, VisitedState varchar(60)
)
Insert into #Bar(EmployeeID, EName, VisitedState)
Select 1, 'Sun', 'U.P.' Union All
Select 1, 'Sun', 'Delhi' Union All
Select 1, 'Sun', 'Other' Union All
Select 2, 'Moon', 'U.P.' Union All
Select 2, 'Moon', 'Delhi' Union All
Select 2, 'Moon', 'Kashmir' Union All
Select 3, 'Tang', 'Other' Union All
Select 3, 'Tang', 'U.P.'
The names are self explanatory.
I need to find employees that have not yet visited the state 'Other'. In the example above, it would be Moon.
I just need their id and name.
Thanks,
Sun
How To Post[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hari.Sharma SSCertifiable Points: 6904 More actions · Answer 1

SELECT DISTINCT EmployeeID,EName FROM #Bar

WHERE EmployeeID NOT IN (SELECT EmployeeID FROM #BAR WHERE VisitedState = 'Other')

Cheers,
Hari
Tips & Tricks for SQL BI Developers

vick.ram79 SSCommitted Points: 1637 More actions · Answer 2

I want to avoid the IN clause as I have heard it is not efficient.

The table above is a heavily simplified version of a table that has hundreds of thousands of records.

Is there another way?

Thanks,

Sun

How To Post[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is another way to do it:

Create Table #Bar (

EmployeeID int

, EName varchar(50)

, VisitedState varchar(60)

)

Insert into #Bar(EmployeeID, EName, VisitedState)

Select 1, 'Sun', 'U.P.' Union All

Select 1, 'Sun', 'Delhi' Union All

Select 1, 'Sun', 'Other' Union All

Select 2, 'Moon', 'U.P.' Union All

Select 2, 'Moon', 'Delhi' Union All

Select 2, 'Moon', 'Kashmir' Union All

Select 3, 'Tang', 'Other' Union All

Select 3, 'Tang', 'U.P.'

;

with Others (

EmployeeID,

VisitedState

) as (

select

EmployeeID,

VisitedState

from

#Bar

where

VisitedState = 'Other'

)

select distinct

b.EmployeeID,

b.EName

from

#Bar b

left outer join Others o

on (b.EmployeeID = o.EmployeeID)

where

o.EmployeeID is null;

drop table #Bar;

vick.ram79 SSCommitted Points: 1637 More actions · Answer 4

Thanks Lynn, this works splendid...

How To Post[/url]