Re: query help

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.
>
> Any help?

If I understand you correctly, you want the query to return the syst_desc
and clinical_text fields where either or both are available? (and not just
where both are available)?

If so a LEFT JOIN may be your best friend. The statement could look like
this:

    SELECT system_id.syst_desc,
           clinical_text.clinical_text
      FROM xref
 LEFT JOIN system_id
        ON xref.syst_id=system_id.syst_id
 LEFT JOIN clinical_text
        ON xref.text_id=clinical_text.text_id
     WHERE xref.drug_id=?               <- insert query value here
       AND xref.function_id=?           <- and here

(disclaimer: statement untested)

The values not available will be returned as NULL.

> Thanks a bunch,

a bunch of what? ;-)


HTH

Ian Barwick

--
Ian Barwick - Developer
http://www.akademie.de

Remove SUNGLASSES to reply ;-)