Re: Search on Child Record Set

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 30 Nov 1998 18:02:25 GMT
Message-ID: <3665dce3.14475704@192.86.155.100>

A copy of this was sent to "Alan D. Mills" <alanm_at_uk.europe.mcd.mot.com> (if that email address didn't require changing) On Mon, 30 Nov 1998 16:49:35 -0000, you wrote:

>I'd like to know how to achieve the following.
>
>Suppose I have two table in a master detail relationship, EMPLOYEE and
>EMP_SKILL.
>
>An employee (hopefully) has many skills. I wish to search for people with a
>skill set which includes all of a search skill set. The skill set I wish to
>search on may have one or more matches to achieve.
>

select *
from employee
where empno in ( select empno

                    from emp_skill
                   where skill in ( 'A', 'B', 'C' )
                   group by empno
                  having count(*) = 3 )

is one way. You would build the IN clause ('A', 'B', 'C' in this case) and set the "count(*) = 3" up as well (3 in this case for 3 in clause pieces, if more or less, adjust 3 accordingly).

You could parameterize this query to a certain degree, for example:

select *
from employee
where empno in ( select empno

                    from emp_skill
                   where skill in (:skill1, :skill2, :skill3, :skill4, :skill5)
                   group by empno
                  having count(*) = :num_skills )

allowing you to bind upto 5 skills (set the others to NULL or some nonlegal skill code) and then set num_skills to the number of skills you are looking for...

>If it were a match against a single skill then it would be quite easy but as
>the list of required skills may be many (let's assume the required skill set
>is stored in another 'detail' table) I'm not sure how to go about it.
>
>I guess I could iterate through the required skills, eliminating any which
>done; have a match but seems a long approach. Is there a nice, single query
>I can throw at it?

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Nov 30 1998 - 12:02:25 CST