joins: avoiding recurrences

From: Torsten <t.lange_at_ufz.de>
Date: 22 Feb 2004 15:39:05 -0800
Message-ID: <7f9d5be9.0402221539.184d7c7@posting.google.com>

Hello,
I have 4 tables:

#1 samples pointing to #2 analyses

 ___________                ___________


|sample_id  |              |analys_id  |

|sample     |              |sample_fid |

|...        |              |           |

-------------              | ...       |
                           -------------

and analyses pointing to

#3 gen_chemistry    and      #4 spec_chemistry
 _____________                ______________


|ge_chem_id   |              |sp_chem_id    |

|analyses_fid |              |analyses_fid  |

|ge_param1    |              |sp_param1     |

|ge_param2    |              |sp_param2     |

| ...         |              | ...          |

---------------              ----------------

(There is also a third parameter table...

That's how it schould look:

|sample|ge_param1|ge_param2|sp_param1|sp_param2|ge_chem_id|sp_chem_id|
|------+---------+---------+---------+---------+----------+----------|
tupel 1 to n

Now I face the problem, that I can't get a select without either recurrences of rows which of course is an effect of joining tables, or no recurrences but the tupels of table #3 and #4 are not at the same row but below each other like

|sam*|ge_p*1|ge_p*2|sam*|sp_p*1|sp_param2|ge_chem_id|sp_chem_id|
|----+------+------+-----------+---------+----------+----------|
|sam1| 6.6| 5.9| | | | 43| |
| | | |sam1| 27.1| 16.7| | 359|
...

How the experienced would do this?

Thank you,
Torsten Received on Sun Feb 22 2004 - 17:39:05 CST