Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Question Unanswered: Finding missing records.

    Hi, I have a table that contains a person contact for a given centre. There may be many types of contact at a centre. However there maybe only say2 mandatory type of contact that must be setup for a centre. If one of these mandatory contacts is not setup then the SQL should list out this centre and the missing mandatory contact type.
    For example, imagine there are 10 sales offices. There can be all types of people work there, receptionist, cleaner, CEO, finance manager(FM) etc. However we just want to check each centre if there is a missing CEO or FM.
    I managed to write two SQL statements.
    1. This is a Cartesian/equi-join that shows who SHOULD be at each centre eg
    select *
    from CENTRES c, staff_roles sr
    where sr.STAFF_ROLE in ('CEO','FM', )
    order by c.centre_no

    Centre Person Role Code
    ‘001’ CEO
    ‘001’ FM
    ‘002’ CEO
    ‘002’ FM
    Etc.
    2. I then managed to write another SQL to show who is ACTUALLY there, eg
    select distinct centre_no, staff_role
    from centrestaff
    where staff_role in ('CEO','FM', )
    order by centre_no, staff_role

    ‘001’ CEO
    ‘002’ CEO
    ‘002’ FM

    So you can see centre 001 is missing a Finance Manager ‘FM’ and should be shown on the final report.

    3. My last thing was to find a way to link these two SQL together in some type of OUTER join and to do a select on the right side where the centre and role code are NULL values.
    select *
    from (select *
    from CENTRES c, staff_roles sr
    where sr.STAFF_ROLE in ('CEO','FM', )
    order by c.centre_no)
    right OUTER JOIN centrestaff NCS ON c.CENTRE_NO = ncs.CENTRE_NO
    and ncs.staff_role in ('CEO','FM', )
    order by ncs.centre_no, ncs.staff_role

    However I keep getting message ORA-00942: table or view does not exist.
    Possible the problem is I am trying to refer to the centre_no in the encapsulating SQL FROM statement. Is there a way round this?
    thanks


  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select *
    from CENTRES c, staff_roles sr
    where sr.STAFF_ROLE in ('CEO','FM', )
    order by c.centre_no
    MINUS
    select distinct centre_no, staff_role
    from centrestaff
    where staff_role in ('CEO','FM', )
    order by centre_no, staff_role;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2012
    Posts
    6
    Thanks for the reply, I tried your suggestion but get an error:
    ORA-00933: SQL command not properly ended 

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    since I don't have the tables or data, I can't actually run or test any SQL for you.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2012
    Posts
    6

    Smile

    Thanks for the MINUS tip.
    The problem was Order by can only be used as the last statement.

Tags for this Thread

Posting Permissions

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