Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    10

    Unanswered: Comparing multiple row results

    Hello folks,

    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

    Helicopter_Type
    (
    ht_nbr number(3),
    ht_name varchar2(15) NOT NULL,
    cost_per_hour number(6,2) DEFAULT 0 NOT NULL,
    nbr_seats number(2) DEFAULT 2 NOT NULL,
    );

    Employee
    (
    employee_nbr number(4),
    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
    );

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

    Thanks

  2. #2
    Join Date
    Jul 2004
    Posts
    62
    Greetings

    SHould it be like this

    select A.ht_nbr,A.ht_name,B.nbr_hours_flown,C.e_lastname from Helicopter_Type A,Endorsement B,Employee C
    where A.ht_nbr=B.ht_nbr AND B.employee_nbr=C.employee_nbr

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Perhaps something like this?
    Code:
    SELECT e.employee_nbr, COUNT(*) 
    FROM endorsment e
    GROUP BY e.employee_nbr
    HAVING COUNT(*) = (SELECT COUNT(*) FROM helicopter_type);

  4. #4
    Join Date
    Aug 2006
    Posts
    10
    Thanks for the posts! Will check it out guys...will let you know how I travel very soon.

  5. #5
    Join Date
    Aug 2006
    Posts
    10

    Talking

    Quote Originally Posted by Littlefoot
    Perhaps something like this?
    Code:
    SELECT e.employee_nbr, COUNT(*) 
    FROM endorsment e
    GROUP BY e.employee_nbr
    HAVING COUNT(*) = (SELECT COUNT(*) FROM helicopter_type);
    You are a genius.

    I knew it would be easy, and sure enough it was.

    Thanks for all your help and sorry for being a n00b

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •