Re: query help

On Fri, 7 Sep 2001, Jeff Patterson wrote:

> This seems like such a basic function that I'm sure I am missing something
> fundamental. I have a table, say xref, whose columns are primary key values for
> other tables. Some of theses keys may be NULL for a given row in xref. I want
> to create a query that returns the corresponding entries in the other tables.
>
> xref:
> drug_id | function_id|syst_id |text_id
> --------------------------------------
> d0001   |     2      |   3    | 3423
> d0001   |     5      |        | 5678
> d0056   |     3      |   5    |
>
> system_id:
> syst_id | syst_desc
> -------------------
> 3       | renal
> 4       | hepatic
> 5       |  respiratory
>
> clinical_text:
> text_id| clinical_text
> -----------------------------------
> 3423   | 'some medical mumbo jumbo'
> 5678   | 'more of the same'
>
> I want the syst_desc and clinical_text (plus other similar data from tables not
> shown) given a drug_id and function_id.

Something like (untested)
select drug_id, function_id, syst_desc, clinical_text from
 (xref left outer join system_id using (syst_id))
  left outer join clinical_text using (text_id);