[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Gnumed-devel] Re: [GENERAL] 'foreign key with default null' problem in
From: |
Stephan Szabo |
Subject: |
[Gnumed-devel] Re: [GENERAL] 'foreign key with default null' problem in allergies view |
Date: |
Fri, 2 May 2003 16:21:29 -0700 (PDT) |
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.