How can I combine three tables in a View or a Procedure?

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|              2|        2|     1

Thank you in advance...

Emilio Received on Wed Dec 31 2003 - 00:43:28 CET