complex query

From: Torsten <t.lange_at_ufz.de>
Date: 20 Feb 2004 04:06:49 -0800
Message-ID: <7f9d5be9.0402200406.11fa102e@posting.google.com>

Hello,
I have 4 tables like this:

 samples_water          analyses
 ________________       ________________


|sw_pid (PrimK)  |     |an_pid (PrimK)  |

|----------------|     |----------------|

|sw_name         |---->|an_fid_sw (ForK)|

|sw_id           |     |an_id           |

|sw_date         |     |an_date         |

|...             |     |...             |

------------------     ------------------
                                    ||
                                    ||
                                    \/                               
 tracer_parameter       an_tracer_water        
 ________________       _________________


|tp_pid (PrimK)  |     |atw_pid (PrimK)  |

|----------------|     |-----------------|

|tp_parameter    |     |atw_fid_an (ForK)|

|tp_type         |---->|atw_fid_tp (ForK)|

------------------     |atw_value        |
tp_parameter like:     |atw_value_err    |
Tritium                |...              |
Carbon-14...           -------------------

...I hope that way the structure is described clearer.

The analysed water samples are connected via the table analyses, which also points to other tables containing analyses parameters like chemistry...

Now my table an_tracer_water has a different look like all other parameter tables. Because more then one tracer analysis can be derived from one sample but each atw_value having its own analyis-ID from the lab (an_id), making it unpossible to store the values for each tracer in columns instead of rows.

What I want to:
I need a view (especially retrieving it via php to an internet browser) like this:

sw_name|atw_value(tp_param.=Tritium)|atw_value(tp_param.=C-14)|...

-------+----------------------------+-------------------------+---
HuL678 |                        5.3 |                      72 |...
------------------------------------------------------------------

If anyone has suggestions how to solve my problem...

Thank you,
Torsten Received on Fri Feb 20 2004 - 06:06:49 CST