Re: SELECT query question
select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. -- João Cândido de Souza Neto SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS Fone: (0XX41) 3033-3636 - JS www.siens.com.br Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: SELECT query question
Should be more efficient to do something like: SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name' UNION SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. -- João Cândido de Souza Neto SIENS SOLUÇÕES EM GESTÃO DE NEGÓCIOS Fone: (0XX41) 3033-3636 - JS www.siens.com.br Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SELECT query question
There are many ways to get the same result. hehehehe Gavin Towey gto...@ffn.com escreveu na mensagem news:30b3df511cec5c4dae4d0d290504753413956dc...@aaa.pmgi.local... Should be more efficient to do something like: SELECT Main_ID FROM Table1 WHERE Source1_Name = 'name' UNION SELECT Main_ID FROM Table2 WHERE Source2_Name = 'name' UNION SELECT Main_ID FROM Table3 WHERE Source3_Name = 'name' -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Monday, July 27, 2009 1:09 PM To: mysql@lists.mysql.com Subject: Re: SELECT query question select * from MainTable MT left join Table1 T1 on MT.Main_ID = T1.MainID left join Table2 T2 on MT.Main_ID = T2.MainID left join Table3 T3 on MT.Main_ID = T3.MainID where T1.Source1_Name = anything or T2.Source2_Name = anything or T3.Source3_Name = anything Not tested. Rytsareva, Inna (I) irytsar...@dow.com escreveu na mensagem news:3c9bdf0e91897443ad3c8b34ca8bdca80218f...@usmdlmdowx028.dow.com... Hello. I have 4 tables: MainTable (Main_ID, Main_Name) Table1 (Source1_ID, Source1_Name, Main_ID) Table2 (Source2_ID, Source2_Name, Main_ID) Table3 (Source3_ID, Source3_Name, Main_ID) And a search box. A user can type any names from Source1_Name or Source2_Name or Source3_Name. I need to get Main_ID How to make it? Thanks, Inna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=gto...@ffn.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select query question
Luis Lebron wrote: I have a test results table that looks like this student_id test_id score 1 1 90 1 1 100 1 1 80 2 1 95 2 1 85 2 1 75 I need to create a query that would give me the average of the top 2 scores per student per test. Following this example, student #1 would have an average of 95 (100 + 90)/2 for test #1 and student #2 would have an average of 90 (95 + 85)/2 Tricky, but doable. SELECT a.student_id, a.test_id, avg(b.score), a.score AS second_highest, max(b.score) AS highest FROM test_results a INNER JOIN test_results b ON a.student_id = b.student_id WHERE a.score = b.score GROUP BY a.student_id, a.test_id, a.score HAVING count(b.score) = 2; I think this ought to work. To see how, try executing it by hand against the sample data. Basically, the WHERE restricts the join to look at combinations where the student has scores at least the value found in a.score, which is needed to rank the scores. The group by allows us to count how many scores are at least as high as the one from 'a'. And, the HAVING clause allows us to isolate scores in 'a' which are second-highest using that information; we then compute the average score that's at least as high as the second-highest value. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
