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

From: Emilio Navarro <enavarro_at_attglobal.net>
Date: Tue, 30 Dec 2003 20:14:04 -0700
Message-ID: <3ff24074_4@news1.prserv.net>

Hello Daniel,

Thank you for your suggestion. I will look into the DECODE function. I did not include all of the columns for each one of the tables just the ones I wanted to show in the View. I guess I should have include the INSERT statements too...

Emilio

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1072839263.290818_at_yasure...
> 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)

> Received on Tue Dec 30 2003 - 21:14:04 CST