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 ;-)