CODEWITHSUNDEEP
mysql
spas2k
spas2k

Reputation: 519

Best way to exclude all results if part of subset?

I'm trying to figure out how to find out what is the best way to query a set of workstations within certain departments that do not have certain applications. A workstation cannot have even one of the problem applications in order to make the list. If it has one of the problem applications I need to omit it completely.

For the set of data, the issue applications would be 111, 555, and 888, so only mach 2 should be selected. Mach 1 and 3 should be omitted. The query has to use IN or be able to query a number of applications to omit.

What is the fastest way to query this? 

Mach_name    App_id
mach 1        111
mach 1        222
mach 1        333
mach 2        333
mach 2        222
mach 3        111
mach 3        333


select distinct mach_name from tablename
where app_id not IN('111', '555', '888')

Thanks for your help.

Upvotes: 1

Views: 41

Answers (1)

xQbert
xQbert

Reputation: 35333

Likely fastest (but volume of data and indexes would matter) if app_ID is indexed and A.Mach_name is indexed I can't see how this could be much improved. as it can short circuit out once a single match is found.

Select A.mach_name 
from tableName A
where not exists (Select 1 from tableName B where A.Mach_name = B.Mach_name
and B.App_ID in ('111','555','888')
Group By A.Mach_name)

Likely next fastest has to generate a full match each time; but still faster than note.

Select A.mach_name 
from tableName A
LEFT JOIn tableName B 
 on A.Mach_name = b.Mach_name
and B.App_ID in ('111','555','888')
where B.Mach_name is null
Group by A.Mach_name

Upvotes: 1

Related Questions