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.Elphick@lfix.co.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