Re: 'foreign key with default null' problem in allergies

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.