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 wan
>t
>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?

junk=# select x.drug_id, x.function_id, s.syst_desc, c.clinical_text from xref as x left join system_id as s on x.syst_id = s.syst_id left join clinical_text as c on x.text_id = c.text_id;
drug_id | function_id | syst_desc | clinical_text
---------+-------------+-------------+--------------------------
d0001 | 2 | renal | some medical mumbo jumbo
d0001 | 5 | | more of the same
d0056 | 3 | respiratory |
(3 rows)

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"But without faith it is impossible to please him; for
he that cometh to God must believe that he is, and
that he is a rewarder of them that diligently seek
him." Hebrews 11:6