Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Search on Child Record Set

Re: Search on Child Record Set

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 02 Dec 1998 04:16:49 GMT
Message-ID: <3664bb8b.2250676@192.86.155.100>


A copy of this was sent to "Neil Hulin" neil.hulin_at_litech.freeserve.co.uk

(if that email address didn't require changing) On Tue, 1 Dec 1998 20:31:39 -0000, you wrote:

>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.
>

HUH??????? What are you talking about.

Lets see, the original requestor says:

>>>Suppose I have two table in a master detail
>relationship, EMPLOYEE and
>>>EMP_SKILL.
So I assume by this they mean:

I have 2 tables:

create table emp( empno number primary key,

                  .... other emp data like names and stuff ... );

create table emp_skill ( empno        number, 
                         skill        varchar(n), 
                         primary key(empno, skill_code),
                         foreign key(empno) references emp(empno) );

(seems logical to me given the information above -- and by the way, this does NOT preclude emp_skill from being an intersection table to a skills table with more information about skills does it. rhetorical question)

>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.
>

WRONG, the following queries are 100% correct -- and if you want to write 1 query -- necessary. they give the right answer (thats my definition of correctness, getting the right answer). They are also fairly efficient (show me something better in a single query).

My queries would work on your EMP and EMP_SKILL tables -- you would use the skill_id# (whats the difference between a SKILL_ID# and a SKILL_CODE btw? sound pretty much the same to me)

Also, you state:

"the code below is WRONG {its not, its 100% correct -- even using your schema if you want} and entirely unnecessary".

then -- you supply NO answer to the question. This is like Fermat with his "little theorem" -- famous mathematician who stated a complex theorem and said the proof was too easy, every one should know it and besides he was out of room on the page so couldn't jot it down. Its still somewhat undecided if in fact fermats little theorem has been proven or not yet to this day (see http://argosy.mta.ca/argosy97-98/112097/features/fermat.htm for more fermat info...).

Given that the original request was:

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

the following queries are one 100% correct, efficient way to do it.

If you know of a better, easier, more fullfilling method, please show us.

>>
>>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.

given your 3nf fully normalized schema, it only gets harder as you would have to use 3 tables all the way from emp, to emp_skills, to skill. any query against that schema would be harder to code and not quite as efficient.  

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 Tue Dec 01 1998 - 22:16:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US