Dear all, I am developing a web application for a biological research institute down here in india. I have some <br>problem in using JOINS. The below is the queries i used,<br><br><b>SQL query<br><br></b><b>File name : <span style="color: rgb(0, 102, 0);">drug</span></b><br>
<span style="color: rgb(255, 102, 102);">select drug_name,dtype from bio_info_schema.drug order by dtype</span><br><br><b>File name:<span style="color: rgb(0, 102, 0);">drug_gene_details</span></b><br>Note : i used both the sql queries seperately, but neither one worked.<br>
<br><span style="font-family: courier new,monospace; color: rgb(255, 102, 102);">SELECT drug.drug_name,drug.route_of_admin,drug.dose_adult,drug.dose_child,drug.adverse_effects,drug_gene.gene_name </span><br style="font-family: courier new,monospace; color: rgb(255, 102, 102);">
<span style="font-family: courier new,monospace; color: rgb(255, 102, 102);">FROM drug,drug_gene </span><br style="font-family: courier new,monospace; color: rgb(255, 102, 102);"><span style="font-family: courier new,monospace; color: rgb(255, 102, 102);">WHERE drug.drug_name = drug_gene.drug_name and drug.drug_name=<dtml-sqlvar drug_name type=string></span><br style="font-family: courier new,monospace; color: rgb(255, 102, 102);">
<br style="font-family: courier new,monospace; color: rgb(255, 102, 102);"><span style="font-family: courier new,monospace; color: rgb(255, 102, 102);">SELECT drug.drug_name,drug.route_of_admin,drug.dose_adult,drug.dose_child,drug.adverse_effects,drug.dtype,drug_gene.gene_name</span><br style="font-family: courier new,monospace; color: rgb(255, 102, 102);">
<span style="font-family: courier new,monospace; color: rgb(255, 102, 102);">FROM drug INNER JOIN drug_gene </span><br style="font-family: courier new,monospace; color: rgb(255, 102, 102);"><span style="font-family: courier new,monospace; color: rgb(255, 102, 102);">ON drug.drug_name = drug_gene.drug_name AND drug.drug_name=<dtml-sqlvar drug_name type=string></span><br>
<br>Below is the DTML code i have used,<br><br><b>File name : <span style="color: rgb(0, 102, 0);">view_drug_detail</span></b><br><span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><table width = 30% border = 1 ></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><tr></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><!-- Table header --></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><th>Drug Name</th></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"></tr></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><!-- Iterate through drug details using dtml-in tag --></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><dtml-in drug></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><!-- for each drug , print out the drug name and type of drug --></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><tr></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><td align = center></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"><a href="<a href="http://localhost:8080/ddrd/ddrtb/drugs/display_drugs/">http://localhost:8080/ddrd/ddrtb/drugs/display_drugs/</a><b><span style="color: rgb(0, 102, 0);">drug_gene_details</span></b>/drug_name/<dtml-var drug_name>/<b><span style="color: rgb(0, 102, 0);">view_drug_info</span></b>"><dtml-var drug_name></a></td></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"></tr></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"></dtml-in></span><br style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);">
<span style="font-family: courier new,monospace; background-color: rgb(255, 255, 255); color: rgb(255, 0, 0);"></table><br><br></span><b>File name : <span style="color: rgb(0, 102, 0);">view_drug_info</span></b><br>
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><h3><font color= #008000><center>Drug Information Summary</center></font></h3></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><table width = 100% border = 1 align=center></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Drug name</th><td><dtml-var drug_name></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Route of Administration</th><td><dtml-var route_of_admin></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Dosage (Adult)</th><td><dtml-var dose_adult></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Dosage (Child)</th><td><dtml-var dose_child></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Adverse Effects</th><td><dtml-var adverse_effects></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Drug Type</th><td><dtml-var dtype></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><th>Drug Targets</th><td><dtml-var gene_name></td></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></tr></span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"></table></span><br>
<br>This is the error which i got.<br> <br><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">Site Error</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">An error was encountered while publishing this resource.</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">exceptions.KeyError</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">Sorry, a site error occurred.</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">Traceback (innermost last):</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * Module ZPublisher.Publish, line 150, in publish_module</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * Module ZPublisher.Publish, line 114, in publish</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * Module Zope, line 134, in zpublisher_exception_hook</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * Module Shared.DC.ZRDB.DA, line 547, in __getattr__</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">KeyError: _r</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">Troubleshooting Suggestions</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * The URL may be incorrect.</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;"><span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * The parameters passed to this resource may be incorrect.</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;"> * A resource that this resource relies on may be encountering an error.</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">For more detailed information about the error, please refer to the HTML source for this page.</span><br style="color: rgb(255, 102, 102); font-family: courier new,monospace;">
<span style="color: rgb(255, 102, 102); font-family: courier new,monospace;">If the error persists please contact the site maintainer. Thank you for your patience. </span><br><br>Any kind of suggestion and help will be very useful. Thanks in advance.<br clear="all">
<br>-- <br>Best Regards<br>\madhan<br><br>Things must be learned only to be unlearned again or, more likely, to be corrected.<br> -- Richard Feynman