My team is developing a database with the following tables (minus the constraints to save space here ), amongst quite a number of others in Oracle 9i
ht_name varchar2(15) NOT NULL,
cost_per_hour number(6,2) DEFAULT 0 NOT NULL,
nbr_seats number(2) DEFAULT 2 NOT NULL,
e_lastname varchar2(15) NOT NULL,
e_firstname varchar2(15) NOT NULL,
e_address varchar2(20) NOT NULL,
e_suburb varchar2(15) NOT NULL,
e_postcode number(4) NOT NULL,
e_contact varchar2(12) NOT NULL,
position varchar2(15) NOT NULL
employee_nbr number(4), /*MOD*/
ht_nbr number(3), /*MOD*/
nbr_hours_flown number(6,2) DEFAULT 0 NOT NULL,
last_annual_review date DEFAULT SYSDATE NOT NULL
Basically, for this job we have several helicopters, and an employee (shown as the employee_nbr field) may have multiple endorsements - i.e. one for every kind of helicopter.
An employee may have no endorsements, or endorsements for any number of helicopter types, including the possibility of being qualified to fly everything (which one employee is at present). The endorsement table breaks the M:N relationship between employee and helicopter_type
Now, I am trying to write a query that will tell me ONLY they employees that have endorsements for EVERY kind of helicopter listed in helicopter_type.
I have been fooling around, and yes I know it is probably obvious, but deadlines are breathing down my neck and I need some help here...