Re: [Gnumed-devel] need SQL help

[richard terry]

This is easily solvable if you can use joins, which when used appropriately 
don't care if there is null data in a particular field.

 I've never mucked around with postgres but I enclose an sql from my contacts 
database as an example.

Using a database with a visual data designer with the ability to execute and 
test the joins is great - I guess that's why simpletons like me use msAccess!

The query enclosed executes in a millisecond across scads of tables across the 
network on the server

Not sure how postgres handles this, you need a postgres expert.

Regards

BTW what is the hypo stuff doing in the allergy table, what relevance is it as  
it is not allergy specific. Only allergy specific information should be in 
there.

I'm off to Tocal - an agricultural field day up the Hunter Valley, will catch 
up on mail tonight

Richard


On Sat, 3 May 2003 01:03 am, Karsten Hilbert wrote:
> 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

join sql example.txt
Description: Text document