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

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

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