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:
- List all male employees having some (any) skill level in
SH_SKILL_CD='CRANE1'.
- 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!)
- 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:
- 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;
- 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;
- 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
Received on Thu Jan 03 2002 - 18:57:15 CST
