Re: joins: avoiding recurrences

From: Torsten <t.lange_at_ufz.de>
Date: 23 Feb 2004 04:29:28 -0800
Message-ID: <7f9d5be9.0402230429.7c0043fa@posting.google.com>

Hello, the Orcale version is 9.2.0.4

Table structure:

---

sw_ from sample table (sample_water)
an_ is the (sw ->)analysis(-> parameter) - link table (analyses)

acw_ would be the gen_parameter table (an_chemistry_water) 
axw_ would be the spec_parameter table (an_xenobiotics_water)
atw_ is the third parameter table (an_tracer_water)

Keys:

---

_pid: PrimKey of a table
acw_fid_an: ForKey in acw_ belongig to the PrimKey in a analyses (an_)

With these two I reached my "best" appraoches...

---

#1:
SELECT sw_id,

       sw_date,
       acw_li,
       acw_li_err,
       axw_atrazine,
       axw_atrazine_err,
       an.an_analysis_id,

FROM   an_chemistry_water,
       an_tracer_water,
       analyses an,
       samples_water,
       (SELECT axw_atrazine, axw_atrazine_err, an_analysis_id, an_pid
        FROM an_xenobiotics_water,
             analyses,
             samples_water
        WHERE (axw_fid_an = an_pid)
          AND (an_fid_sw = sw_pid))

WHERE (acw_fid_an(+) = an.an_pid) 

  AND (an.an_fid_sw = sw_pid)
  AND (an.an_pid NOT IN

(SELECT an_pid FROM analyses, an_tracer_water

                 WHERE an_pid = atw_fid_an));

Result #1:

---

|sam*|acw_Li|acw_Li_err|axw_atr*|axw_atr*_err|an_analysis_id|
|----+------+----------+--------+------------+--------------+
|sam1|   6.6|       5.9|    27.1|        16.7|             1|
|sam1|   6.6|       5.9|    27.1|        16.7|             1|
|sam1|   6.6|       5.9|    27.1|        16.7|             1|
|sam1|      |          |    27.1|        16.7|             3|
|sam1|      |          |    27.1|        16.7|             3|
|sam1|      |          |    27.1|        16.7|             3|
|sam2|   8.2|       3.3|    27.1|        16.7|             5|
|sam2|   8.2|       3.3|    27.1|        16.7|             5|
|sam2|   8.2|       3.3|    27.1|        16.7|             5|

 ...

---

-> sam2 was only analysed on general chemistry

# 2:
CREATE OR REPLACE VIEW ancw AS

    SELECT sw_id, acw_li, acw_li_err, an_pid as acw_analysis_id     FROM an_chemistry_water, analyses, samples_water     WHERE (acw_fid_an = an_pid) AND (an_fid_sw = sw_pid);

CREATE OR REPLACE VIEW anxw AS

    SELECT sw_id, axw_atrazine, axw_atrazine_err, an_pid as axw_analysis_id     FROM an_xenobiotics_water, analyses, samples_water     WHERE (axw_fid_an = an_pid) AND (an_fid_sw = sw_pid);

SELECT c.sw_id, x.sw_id,

       acw_li, acw_li_err, axw_atrazine, axw_atrazine_err,
       c.acw_analysis_id, x.axw_analysis_id

FROM ancw c, anxw x, analyses an

WHERE (x.axw_analysis_id(+) = an.an_pid)
  AND (c.acw_analysis_id(+) = an.an_pid)
  AND (an.an_pid NOT IN


                (SELECT an_pid

                 FROM analyses, an_tracer_water
                 WHERE an_pid = atw_fid_an));

Result #2:

---

|sam*|sam*|acw_Li|acw_Li_err|axw_atr*|axw_atr*_err|acw_an*_id|axw_an*_id|
|----+----+------+----------+--------+------------+----------|----------|
|sam1|    |   6.6|       5.9|        |            |         1|          |
|    |sam1|      |          |    27.1|        16.7|          |         3|
|sam2|    |   8.2|       3.3|        |            |         5|          |

 ...

---

-> this is the best approach. Aim is to have only 1 sample column and rows

   belonging to each other should be merged.

I hope you can reconstruct...

Thank you,
Torsten Received on Mon Feb 23 2004 - 06:29:28 CST