Schalk,
Your query looks ok except for an apparently missing '%' in LIKE 'TES', but all the WHEREs are hard to read, aren't they? I find queries easier to write, read & maintain with joins in the JOIN clause and specific row-selection criteria in the WHERE clause, eg
SELECT
demographic.demographic_no, ... etc ...
FROM demographic
INNER JOIN allergies ON demographic.demographic_no = allergies.demographic_no
INNER JOIN drugs ON demographic.demographic_no - drugs.demographic_no
INNER JOIN research ON demographic.demographic_no = research.demographic_no
INNER JOIN echart ON demographic.demographic_no = echart.demographic_no
INNER JOIN dxresearch ON demographic.demographic_no = dxresearch.demographicNo
INNER JOIN ichppccode ON dxresearch.dxresearch_code = ichppccode.ichppccode
WHERE demographic.demographic_no = '1'
AND demographic.last_name LIKE 'TES%'
PB
-----
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
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!
mathias fatene wrote:
Hi, Select * from table1 T1, table2 T2, table3 T3, table4 T4, table5 T5, table6 T6 Where T1.col=T2.col and T2.col=T3.col and T3.col=T4.col and T4.col=T5.col and T5.col=T6.col and T1.col=T6.col [and col='val']
Doesn't this work ? Have you an example ?
Best Regards -------------------- Mathias FATENE
Hope that helps *This not an official mysql support answer
-----Original Message-----
From: Schalk Neethling [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 00:52
To: mysql@lists.mysql.com
Subject: joining six tables by mutual column
Greetings everyone.
Hope someone can give me some pointers here. I have six tables in the database and I need to JOIN them on a row that appears in all of the tables. How do I do this? I have so far done the normal 'cross-join' saying SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE something = something;
I have also added STRAIGHT_JOIN to force the order but, how do I JOIN six tables to/by one column? I have done some google searches as well as
looked at MySQL 2nd edition by Paul DuBois, sorry if I missed something here Paul, and so far I have not found an answer. Any help or pointers will be appreciated. Thank you.
-- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.1 - Release Date: 5/2/2005
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
