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 query reformatted:
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;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
and
allergies.demographic_no = echart.demographicNo
Either one is sufficient to identify the correct row in echart.
Putting all this together, I'd suggest:
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
JOIN demographic
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 demographic?)
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
