[Gnumed-devel] Re: [GENERAL] 'foreign key with default null' problem in allergies view

[Stephan Szabo]

On Sat, 3 May 2003, Karsten Hilbert wrote:

> create table clin_narrative (
>       id serial primary key,
>       id_patient integer not null,
>       src_table name,                                 -- references 
> pg_class(relname) ??
>       value text
> ) inherits (audit_clinical);
>
> -- --------------------------------------------
> create table allergy (
>       id serial primary key,
>       id_clin_transaction integer not null references clin_transaction(id),
>       substance varchar(128) not null,
>       substance_code varchar(256) default null,
>       generics varchar(256) default null,
>       allergene varchar(256) default null,
>       atc_code varchar(32) default null,
>       id_type integer not null references _enum_allergy_type(id),
>       reaction text default '',
>       generic_specific boolean default false,
>       definate boolean default false,
>       had_hypo boolean default false,
>       id_comment integer references clin_narrative(id) default null
> ) inherits (audit_clinical);
> -- ===================================================================

> create view v_i18n_patient_allergies as
> select
>       a.id as id,
>       vpt.id_patient as id_patient,
>       a.id_clin_transaction as id_clin_transaction,
>       a.substance as substance,
>       a.substance_code as substance_code,
>       a.generics as generics,
>       a.allergene as allergene,
>       a.atc_code as atc_code,
>       a.reaction as reaction,
>       a.generic_specific as generic_specific,
>       a.definate as definate,
>       a.had_hypo as had_hypo,
>       _(at.value) as type,
>       cn.value as "comment"
> from
>       allergy a, _enum_allergy_type at, clin_narrative cn, 
> v_patient_transactions vpt
> where
> --    cn.id=a.id_comment
> --            and

My guess is that you want a left outer join between a and cn probably on
(a.id_comment=cn.id).  That way if there is no matching row (such as when
a.id_comment is null) you get effectively an output row with NULLs for the
cn portion.