Merging 14 fields with related tables

[Klaus Roelsgaard]

Background
I´m developing a database for collecting data from PDA's. In my event table
I need to have 14 fields almost similar fields (DiagAndet; DiagEndokrin;
...) Each field consists of an integer which relates to a key field in another
table which have the fields "Key, and "Diagnosis". In each record, 13 fields
are "0" (= no registration). One "active" field contains another number.

I need to have the information from the "Diagnosis" field in the related
table of the "active" field for each record merged in one field for the output.
Is that possible?

[Adelle Hartley]

Klaus Roelsgaard <hekla@dadlnet.dk> wrote in message
news:3aa2c476$1@news.devx.com...
>
> Background
> I´m developing a database for collecting data from PDA's. In my event
table
> I need to have 14 fields almost similar fields (DiagAndet; DiagEndokrin;
> ..) Each field consists of an integer which relates to a key field in
another
> table which have the fields "Key, and "Diagnosis". In each record, 13
fields
> are "0" (= no registration). One "active" field contains another number.
>
> I need to have the information from the "Diagnosis" field in the related
> table of the "active" field for each record merged in one field for the
output.
> Is that possible?

Hey, nothing is impossible in SQL :-)

I'm not sure I understand your problem completely, but this sounds like
it calls for COALESCE (I think this function is specific to SQL server, but
there is probably a similar function in most systems).

A simple version for just 4 fields/tables:

SELECT Coalesce(Coalesce(Coalesce(
RelatedTable1.Diagnosis, RelatedTable2.Diagnosis),
RelatedTable3.Diagnosis),
RelatedTable4.Diagnosis)
FROM (((MainTable LEFT JOIN RelatedTable1 ON
MainTable.Key1=RelatedTable1.Key)
LEFT JOIN RelatedTable2 ON MainTable.Key2=RelatedTable2.Key)
LEFT JOIN RelatedTable3 ON MainTable.Key3=RelatedTable3.Key)
LEFT JOIN RelatedTable4 ON MainTable.Key4=RelatedTable4.Key

I've taken you at your word when you say that the Key values in the main
table
are zero for all but one column. If there is more than one non-zero value,
then
the above query will return the diagnosis for the first non-zero value it
encounters.

Adelle.
_____________________________________________________________
Impress your friends. Become an SQL guru.
http://www.sql-guru.com