Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Question Unanswered: Grouping-Interesting

    I have 3 tables.

    One has courses and Role columns CR table.
    2nd has Role and employee id RE table
    3rd has Emp Id, course, completion date.


    One employee has multiple roles and each role has some courses associated with it. For eg, Emp # 111 can have role of developer and analyst and for developer he needs to finish course c1, c2, and c3.

    He is given credit for developer course completion only after he completes all the courses.

    I am querying these tables from a remote db. I need to check which employees have finished all the courses that maps to his role. If he has finished them then i go ahead with further processing.

    Can anyone suggest a good solution.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I'm curious.
    Which school, class & instuctor assigned you this homework?

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    Employees who have not completed all courses:
    Code:
    select empid from(
    select empid, course from table1, table2 where table1.role=table2.role
    MINUS
    select empid, course from table3 group by empid, course)
    Employees who have completed all courses
    Code:
    select empid from table3
    MINUS
    (
    select empid from(
    select empid, course from table1, table2 where table1.role=table2.role
    MINUS
    select empid, course from table3 group by empid, course)
    )
    Oracle can do wonders !

  4. #4
    Join Date
    Jan 2004
    Posts
    4

    can u review

    Can you review this code for same

    **********************************

    select crm.crol_trol_cd, mcd.mcd_mem_id,
    (select distinct count(*)
    from cardtrack.course_role_map crm1
    where crm1.crol_trol_cd = crm.crol_trol_cd)
    as cnt,
    (select distinct count(*)
    from cardtrack.course_role_map crm2
    where crm2.crol_trol_cd = crm.crol_trol_cd
    and crm2.crol_cr_cd in (select mcd1.mcd_cr_cd
    from member_course_details mcd1
    where mcd1.mcd_mem_id = mcd.mcd_mem_id))
    as cnt1
    from cardtrack.member_course_details mcd, cardtrack.course_role_map crm
    where mcd.mcd_cr_cd = crm.crol_cr_cd

    ***********************************

  5. #5
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    also provide the tables and table descirptions.
    Oracle can do wonders !

  6. #6
    Join Date
    Jan 2004
    Posts
    4
    Originally posted by cmasharma
    also provide the tables and table descirptions.
    The three tables I mentioned,
    One has courses and Role columns is cardtrack.course_role_map
    One which has Emp Id, course, completion date is cardtrack.member_course_details
    crol_cr_cd is course_id
    mcd_mem_id is mem_id
    mcd_cr_cd is course id in 2nd table.

    I am not able to understand the coorelated subquery.

Posting Permissions

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