Re: joining six tables by mutual column
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. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining six tables by mutual column
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]
Re: joining six tables by mutual column
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]
RE: joining six tables by mutual column
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. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining six tables by mutual column
Try this If something is numeric SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE table1.something = table2.something AND table1.something = table3.something AND table1.something = table4.something AND table1.something = table5.something AND table1.something = table6.something ; If something isn't numeric. SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE table1.something LIKE table2.something AND table1.something LIKE table3.something AND table1.something LIKE table4.something AND table1.something LIKE table5.something AND table1.something LIKE table6.something ; ( Nick Pasich ) On Mon, Apr 25, 2005 at 12:51:53AM +0200, Schalk Neethling wrote: 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. -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Business.Solution.Developers emotionalize.conceptualize.visualize.realize Landlines Tel: +27125468436 Fax: +27125468436 Web email:[EMAIL PROTECTED] Global: www.volume4.com Messenger Yahoo!: v_olume4 AOL: v0lume4 MSN: [EMAIL PROTECTED] We support OpenSource Get Firefox!- The browser reloaded - http://www.mozilla.org/products/firefox/ This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining six tables by mutual column
On Sun, April 24, 2005 3:51 pm, Schalk Neethling said: 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; SELECT * FROM table1, table2, table3, table4, table5, table6 WHERE table1.something = table2.something AND table2.something = table3.something AND table3.something = table4.something AND table4.something = table5.something AND table5.something = table6.something Make sure 'something' has an index on each table. Also, don't use SELECT * unless you really NEED every column, and always will, no matter how the schema changes. If there's any chance at any time in the future that you won't need EVERY column, then specify each and every column you need individually: SELECT table1.something, table1.whatever, table2.something_else -- Like Music? http://l-i-e.com/artists.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
