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.