"R. P." <r_pol12gar_at_hotmail.com> wrote in message news:<L57Z7.323482$W8.12364525_at_bgtnsc04-news.ops.worldnet.att.net>...
> Dear SQL experts:
>
> I am familiar with table joins but not very experienced with
> sophisticated SQL queries on them. So I need help in coming
> up with some efficient SELECT statement for a query that is
> illustrated in the following simplified examples.
>
> Let's say I have two tables: an EMPLOYEE table with the EM_EMP_NO
> as the Primary Key (PK) and a SKILL_HIST table where the SH_EMP_NO
> is a Foreign Key (FK) from the EMPLOYEE table and forms a combined
> Primary Key with SH_SKILL_CD and SH_SKILL_LEVEL to prevent duplication
> of the same skill level for given employee, though one employee may
> have several rows for the same skill as he/she is certified for different
> levels.
>
> Table: EMPLOYEE
>
> Name Null? Type
> ------------ -------- ------------
> EM_EMP_NO NOT NULL NUMBER(6)
> EM_EMP_NAME NOT NULL VARCHAR2(40)
> EM_EMP_ADDR NOT NULL VARCHAR2(40)
> EM_EMP_SEX NOT NULL VARCHAR2(1)
>
> Table: SKILL_HIST
>
> Name Null? Type
> ------------ -------- -------------
> SH_EMP_NO NOT NULL NUMBER(6)
> SH_SKILL_CD NOT NULL VARCHAR2(6)
> SH_SKILL_LEVEL NOT NULL VARCHAR2(4)
> SH_CERT_DATE DATE
>
>
> In all the queries I want matching employee LISTED ONLY ONES and the
> listings
> are sorted by EMP_NAME.
> Unfortunately, using table joins the way I know them would list
> at least some employees more than ones.
>
> Here is the kind of queries I'd like to do:
>
> 1. List all male employees having some (any) skill level in
> SH_SKILL_CD='CRANE1'.
>
> 2. List all female employees who have at least one certified 'MAST' skill
> level,
> regardless of what skill it is.
> (Note: to have a certified skill level, the SH_CERT_DATE may not be
> NULL!)
>
> 3. List all male employees who do NOT HAVE ANY certified 'MAST' skill
> level at all.
>
> The only way I could come up with the desired results is using the EXISTS
> clause in the SELECT statements, as in the following:
>
> 1. SELECT EM_EMP_NAME, EM_EMP_ADDR FROM EMPLOYEE WHERE EM_EMP_SEX = 'M'
> AND EXISTS (SELECT * FROM SKILL_HIST WHERE SH_EMP_NO = EM_EMP_NO
> AND SH_SKILL_CD = 'CRANE1') ORDER BY 1;
>
> 2. SELECT EM_EMP_NAME, EM_EMP_ADDR FROM EMPLOYEE WHERE EM_EMP_SEX = 'F'
> AND EXISTS (SELECT * FROM SKILL_HIST WHERE SH_EMP_NO = EM_EMP_NO
> AND SH_SKILL_LEVEL = 'MAST'AND SH_CERT_DATE IS NOT NULL) ORDER BY 1;
>
> 3. SELECT EM_EMP_NAME, EM_EMP_ADDR FROM EMPLOYEE WHERE EM_EMP_SEX = 'M'
> AND NOT EXISTS (SELECT * FROM SKILL_HIST WHERE SH_EMP_NO = EM_EMP_NO
> AND SH_SKILL_LEVEL = 'MAST'AND SH_CERT_DATE IS NOT NULL) ORDER BY 1;
>
> I suspect this type of use of EXISTS clause is not the most efficient way
> to get the these results. I wonder if any of you can suggest better
> alternatives, especially ones that would also include a count of SKILL_HIST
> rows matching the query criteria (for positive queries, of course.)
>
> Thanks,
> Rudy
Here's how I'd have written the queries:
- select distinct e.em_emp_name, e.em_emp_addr
from employee e, skill_hist s
where e.em_emp_sex = 'M'
and s.sh_skill_cd = 'CRANE1'
and e.em_emp_no = s.em_emp_no
order by 1
- select distinct e.em_emp_name, e.em_emp_addr
from employee e, skill_hist s
where e.em_emp_sex = 'F'
and s.sh_skill_level = 'MAST'
and s.sh_cert_date is not null
and e.em_emp_no = s.em_emp_no
order by 1
- select distinct e.em_emp_name, e.em_emp_addr
from employee e
where e.em_emp_sex = 'M'
and e.em_emp_no not in
(select distinct s.sh_emp_no
from skill_hist s
where s.sh_skill_level = 'MAST'
and s.sh_cert_date is not null)
order by 1
Received on Fri Jan 04 2002 - 13:31:00 CST
