Re: SQL Question

From: Alvin Nonaka <xea0005_at_co.honolulu.hi.us>
Date: 1996/03/29
Message-ID: <Dp1tII.2Cp_at_news.hawaii.edu>#1/1

builder_at_deltanet.com (Boris Veksler) wrote:
>Given:
>Each Experiment (ID) has data (Data) - table 1,
>each data (Data) may produce several different results (Result) - Table 2.
>
>Create
>-> Therefor each experiment may produce several different results - Table 3
>
>I need to write SQL query to do it. Any idea?
>
>Input table 1:
>ID | Data
>-----------
>1 | d1
>2 | d2
>3 | d3
>
>Input table 2:
>Data | Result1 | Result2 | Result3
>------------------------------------
>d1 | r1 | r2 | r3
>d2 | r2 | r3 |
>d3 | r3 | r4 | r5
>
>I need to write a SQL query that will create table like this:
>
>Output table 3:
>ID | Result1 | Result2 | Result3
>------------------------------------
>1 | r1 | r2 | r3
>2 | r2 | r3 |
>3 | r3 | r4 | r5
>
>Thanks
>
>Boris

select T1.ID, T2.Result1, T2.Result2, T2.Result3 from table1 T1, table2 T2, table3 T3
where T1.Data = T2.Data;

is the obvious answer.

You can, in Oracle's SQL*PLUS, create a physical table like this:

create table3 as
select T1.ID, ... -- using same sql statement as above.

But I'd be leary of this (and the 'obvious answer') because you are using 'data' to do the join on -- which makes me uncomfortable.

If the following is true:

Driver ---------------------> Experiment ---------------> Observed
(given a model of a system                            (Measure outputs)

with x,y,z parameters --
vary only one and keeping
all others fixed)

your E-R diagram at a comceptual level would be:

Input -------------< Run >------------------ Result

'Each input type must have 1 or more experimental runs.' 'Each expected result must have one or more test runs.'

create table input (

   input_type number(--), -- pick a number large enough    desc varchar2(80) -- what you are varying );

create table result (

   null_hypothesis_no(--),
   null_hypothesis varchar2(80) -- what you hope to see );

create table run (

   run_no number(--), -- pk, put values in here from a seq generator    run_time date,
   input_type, -- fk to input
   null_hypothesis_no, -- fk to result
   input_data1 <value type>, -- your data -- vary inputs

   result_data1 <value type>,-- your result(s) --measure of obs. behavior
   result_data2 ...
   result_data<n>

);

alter table run (

   ... add pk and fk constraints
);

A query on summarized values from the run table will get you the values that you want.

Boris, your table3 is really the summarized query on the run table, flipped on its side. (For more info, see Koch/Loney, "Oracle the Complete Reference, 3e", pp.365-7 -- 'Flipping a Table onto Its Side').

aloha,
ayn Received on Fri Mar 29 1996 - 00:00:00 CET