Re: How to combine 3 tables into a View or procedure?

Emilio Navarro wrote:
> Hello everyone. I hope someone can help me with this. I have 3
> columns called EMPLOYEE_TEST, TEST_QA and EMPLOYEE_TEST_ANSWER.
>
> The TEST_QA table contains the following:
>
> TEST_QA_ID | PROC_TEST_ID | TEST_QA_QUESTION | TEST_QA_CORRECT
> -----------+--------------+------------------+-----------------
> 3| 1|First Question | 5
> 4| 2|Second Question | 1
> 5| 1|Third Question | 2
> 6| 3|Fourth Question | 1
> 7| 3|Fifth Question | 5
> 8| 3|Sixth Question | 2
> 9| 3|Seventh Question | 3
>
> The EMPLOYEE_TEST table contains the following:
>
> EMPLOYEE_ID | EMPLOYEE_TEST_ID
> ------------+------------------
> 3| 7
>
> The EMPLOYEE_TEST_ANSWER table contains the following:
>
> EMPLOYEE_TEST_ID | TEST_QA_ID | TEST_QA_CORRECT
> -----------------+------------+-----------------
> 7| 9| 5
> 7| 8| 2
> 7| 6| 1
>
> From these three tables I can see that the employe with ID = 3 has
> answered 3 questions and two of them are correct. What I would like
> to be able to do is to represent this in a view (if possible)
>
> EMPLOYEE_ID | EMPLOYEE_TEST_ID | TOTAL_ANSWERS | CORRECT | WRONG
> ------------+------------------+---------------+---------+-------
> 3| 7| 3| 2| 1
>
> Thank you in advance...
>
> Emilio

Maybe you can see it but I can't.

What I can tell you is that you can cross-tab using the DECODE function and create a view from that.

Me? I'd start by restructuring the tables. Any relationship between this and third-or-fourth normal form appears to be coincidental.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)