Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: I think I have a design issue

    So, have a situation like the following

    Code:
    create table company (pk number, srvy_year number);
    create table questions (pk number, srvy_year  number);
    create table responses (pk number, fk_company number, fk_questions number);
    
    insert into company values (1, 2006);
    insert into company values (2, 2007);
    
    insert into questions values (1, 2006);
    insert into questions values (2, 2006);
    insert into questions values (3, 2006);
    insert into questions values (1, 2007);
    insert into questions values (2, 2007);
    
    insert into responses values (1, 1, 1);
    insert into responses values (2, 1, 2);
    insert into responses values (3, 2, 1);
    
    select company.pk, questions.pk, responses.pk
    from   company, questions, responses
    where  company.srvy_year   = questions.srvy_year and 
           company.pk   = responses.fk_company(+) and
           questions.pk = responses.fk_questions(+)
    but I get the error in the query

    Code:
    ORA-01417: a table may be outer joined to at most one other table
    For a description of the business rules:
    We have a 20-question questionnaire each year, and all companies answer those same 20 questions. So, when we store their responses, we keep the company primary key and the question primary key in the responses table. Each company may not answer all 20 questions, but we still want to show those questions with a blank answer, hence the outer join to the responses table.

    What am I doing wrong here?
    Thanks,
    Chuck

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >company.pk = responses.fk_company(+) and
    My 1st take on this join condition is that the outer join would/should only come into play it a company submits NO response(s).
    IMO, a report for this condition can & should be separately reported.
    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
    Dec 2003
    Posts
    1,074
    There's one record in the response table for each question answered, and each response pertains to a company and a particular question. So, if someone didn't answer 1 question in a 20-question questionnaire, they'd have 19 records in the responses table.

    That's why there are 2 outer joins, because the response must track back to which question is being answered, and who is the answering it - and then we have to account for non-responses.

    --=-cf

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    At a minimum srvy_year is needed in RESPONSE table
    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
    Dec 2003
    Posts
    1,074
    Good catch, but then I'd have 3 outer joins, which would result in the same error.

    Am I supposed to add null responses to the "responses" table, so there's always a matching number of responses and questions asked?

    ---=Chuck

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I think I'll set up a UNION ALL stmt where the first query uses equi-joins, and the second has a NOT EXISTS subselect going against the responses table. That solves the problem, but I have this nagging feeling that there's something inherently wrong with the design.

    --=cf

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    As presented, IMO, the COMPANY table can & should be eliminated or it could be offered as a view (assuming RESPONSES table contains SVRY_YEAR).
    Code:
    CREATE OR REPLACE VIEW COMPANY
    AS SELECT DISTINCT FK_COMPANY, SVRY_YEAR FROM RESPONSES
    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.

Posting Permissions

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