[Gnumed-devel] need SQL help

[Karsten Hilbert]

Horst, Ian, others,

I am trying to set up a view that displays the allergies per
patient. Problem being that in the allergy table the
id_comment is a foreign key into clin_narrative which can be
NULL if no comment is recorded. I can't figure out how to
select the rows I need !

select a.substance, cn.value
    from
        allergy a, clin_narrative cn
    where
        a.id_comment=cn.id;

This does not work because a.id_comment can be NULL.

select a.substance, cn.value
    from
        allergy a, clin_narrative cn
    where
        a.id_comment in (cn.id, NULL);

does not work either although it seems intuitive.

Any suggestions ?

For the full SQL definitions see server/sql/gmclinical.sql and
gmClinicalViews.sql.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346