Re: Search on Child Record Set

Thomas Kyte wrote in message
<3665dce3.14475704_at_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.
>>

EMP and EMP_SKILL is not a master-detail relation. EMP_SKILL is a 3nf intersecting entity.

Consider...
EMP[emp_id#,surname, first_name, middle_initial, emp_code]
SKILL[skill_id#,skill_description, skill_code] EMP_SKILL[emp_id#, skill_id#]

Trying to resolve this using the code below is WRONG and entirely unnecessary.

>
>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 )
>/

<snip>

Then it isn't hard to create some very efficient sql to resolve all of your requirements.
--
...neil {actually: neil [dot] hulin [at] litech [dot] freeserve [dot] co [dot] uk} Received on Tue Dec 01 1998 - 14:31:39 CST