Schalk Neethling wrote:
> Mathias/Everyone on the list
> I am running the following query against the database:
> SELECT demographic.demographic_no, demographic.first_name,
> demographic.last_name, demographic.chart_no, demographic.sex,
> demographic.year_of_birth, demographic.month_of_birth,
> demographic.date_of_birth, demographic.family_doctor,
> demographic.roster_status, demographic.patient_status, demographic.phone
> FROM allergies, demographic, drugs, echart, dxresearch, ichppccode WHERE
> demographic.demographic_no = '1' AND demographic.last_name LIKE 'TES'
> AND allergies.demographic_no = demographic.demographic_no AND
> demographic.demographic_no = drugs.demographic_no AND
> drugs.demographic_no = dxresearch.demographic_no AND
> dxresearch.demographic_no = echart.demographicNo AND
> allergies.demographic_no = echart.demographicNo AND
> dxresearch.dxresearch_code = ichppccode.ichppccode
First, some general advice. As presented above, your query is very
difficult to read. People who could help you may choose to ignore you
if they have to work too hard to understand your question. You should
take a few minutes to rewrite your query in an easily readable format,
so those who would help you don't have to. As an example, here's your
FROM allergies, demographic, drugs, echart, dxresearch, ichppccode
WHERE demographic.demographic_no = '1'
AND demographic.last_name LIKE 'TES'
AND allergies.demographic_no = demographic.demographic_no
AND demographic.demographic_no = drugs.demographic_no
AND drugs.demographic_no = dxresearch.demographic_no
AND dxresearch.demographic_no = echart.demographicNo
AND allergies.demographic_no = echart.demographicNo
AND dxresearch.dxresearch_code = ichppccode.ichppccode;
Longer, yes, but much easier to read.
Now that I can read it, two things jump out. Assuming demographic_no is
a number, you would save a string to number conversion if you would
leave out the quotes around the desired number, 1. Secondly, equality
comparisons seem to be faster than LIKE comparisons. If there is no
wildcard in the string to match, use = instead of LIKE.
> Is the string at the end from, 'AND allergies.demographic_no =
> demographic.demographic_no...', the correct way to create the JOIN I
> require by demographic_no on five of the tables and then a JOIN of the
> dxresearch and ichppccode tables by dxresearch_code and ichppccode
> respectively? Any help or pointers would be much appreciated. Thank you!
Yes, this should work, but you would be better served if you took Shawn
Green's standard advice: Use explicit JOIN conditions. That is, move
the JOIN criteria from the WHERE clause to the ON part of the JOIN
clause. If you rewrite the above that way, you will notice that you
have redundant conditions:
dxresearch.demographic_no = echart.demographicNo
allergies.demographic_no = echart.demographicNo
Either one is sufficient to identify the correct row in echart.
Putting all this together, I'd suggest:
ON allergies.demographic_no = demographic.demographic_no
JOIN drugs ON demographic.demographic_no = drugs.demographic_no
JOIN dxresearch ON drugs.demographic_no = dxresearch.demographic_no
JOIN echart ON dxresearch.demographic_no = echart.demographicNo
JOIN ichppccode ON dxresearch.dxresearch_code = ichppccode.ichppccode
WHERE demographic.demographic_no = 1
AND demographic.last_name = 'TES';
(I take it demographic.demographic_no is not the PRIMARY KEY in table