"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
>
>
- SELECT EM_EMP_NAME, EM_EMP_ADDR, COUNT(*) FROM EMPLOYEE, SKILL_HIST
WHERE EM_EMP_SEX = 'M' AND SH_EMP_NO = EM_EMP_NO AND SH_SKILL_CD = 'CRANE1'
group by EM_EMP_NAME, EM_EMP_ADDR
order by 1
- SELECT EM_EMP_NAME, EM_EMP_ADDR, COUNT(*) FROM EMPLOYEE, SKILL_HIST
WHERE EM_EMP_SEX = 'F' AND SH_EMP_NO = EM_EMP_NO AND SH_SKILL_LEVEL = 'MAST'
AND SH_CERT_DATE IS NOT NULL
group by EM_EMP_NAME, EM_EMP_ADDR
order by 1
- is OK.
Received on Fri Jan 04 2002 - 10:07:28 CST
