Обсуждение: query help
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? Thanks a bunch, Jeff Patterson The Melanoma Patients' Information Page http://www.mpip.org -- Jeff Patterson Site Administrator The Melanoma Patients' Information Page http://www.mpip.org/
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 ;-)