O'Reilly logo

The Art of SQL by Peter Robson, Stephane Faroult

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Selecting Rows That Match Several Items in a List

An interesting problem is that of how to write queries based on some criteria referring to a varying list of values. This case is best illustrated by looking for employees who have certain skills, using the three tables shown in Figure 11-6. The skillset table links employees to skills, associating a 1 to 3 skill_level value to distinguish between honest competency, strong experience, and outright wizardry.

Tables used for querying employee skills

Figure 11-6. Tables used for querying employee skills

Finding employees that have a level 2 or 3 SQL skill is easy enough:

select e.employee_name
from employees e
where e.employee_id in
     (select ss.employee_id
      from skillset ss,
           skills s
      where s.skill_id = ss.skill_id
        and s.skill_name = 'SQL'
        and ss.skill_level >= 2)
order by e.employee_name

(We can also write the preceding query with a simple join.) If we want to retrieve the employees who are competent with Oracle or DB2, all we need to do is write:

select e.employee_name, s.skill_name, ss.skill_level
from employees e,
     skillset ss,
     skills s
where e.employee_id = ss.employee_id
  and s.skill_id = ss.skill_id
  and s.skill_name in ('ORACLE', 'DB2')
order by e.employee_name

No need to test for the skill level, since we will accept any level. However, we do need to display the skill name; otherwise, we won't be able to tell why a particular employee was returned by the query. We also encounter ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required