Simple JOIN on three tables
Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 12:46, spacemarc said: Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it Well that's what you asked it to do, if you look closely at your query. How are table a, b, and c related to each other? Do you want all the information from each of the tables (which are NOT related to each other) between the specified dates? If that is the case you are looking at a UNION rather than a strait JOIN. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
Cartesian Join Anyone ??? Rearrange Query as a UNION instead - Original Message - From: spacemarc [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, October 18, 2006 3:46:04 PM GMT-0500 US/Eastern Subject: Simple JOIN on three tables Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it -- 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: Simple JOIN on three tables
ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
But if I wanted to use a join I can make it however or not? You can join on any row(s) you like. What are you trying to acccomplish? PB spacemarc wrote: ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:21, spacemarc said: ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- http://www.spacemarc.it A JOIN implies that the tables are related, that is, a column (or columns) in table a has the same value as a column (or columns) in table b. Thats what they mean when they call it a RELATIONAL database. If the tables don't have such a relationship you are asking for the UNION of the separate information from table a, where it meets its WHERE conditions, and the information from table b where it meets its WHERE conditions. The fact that both where conditions are the same doesn't matter. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
2006/10/18, Peter Brawley [EMAIL PROTECTED]: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:37, spacemarc said: 2006/10/18, Peter Brawley [EMAIL PROTECTED]: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- http://www.spacemarc.it Then it is really a UNION. I hope you have the date field as an index otherwise you are looking at a table scan which is always slow. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
2006/10/18, William R. Mussatto [EMAIL PROTECTED]: Then it is really a UNION. I hope you have the date field as an index otherwise you are looking at a table scan which is always slow. Ok, if I have the field ID that have the same value in three tables but I want to select however the data based on the date (-MM-DD) -- http://www.spacemarc.it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
I simply want to select the records from my three tables that are comprised in time interval. If you want the results in one resultset, apply an appropriate Where clause to each year-table query and union the queries, eg SELECT * FROM a WHERE date BETWEEN '2004-3-1' AND '2004-6-30' UNION SELECT * FROM b WHERE date BETWEEN '2005-3-1' AND '2005-6-30' etc PB - spacemarc wrote: 2006/10/18, Peter Brawley [EMAIL PROTECTED]: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.408 / Virus Database: 268.13.5/483 - Release Date: 10/18/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables In addition to the other answers -- This sounds like a really bad database design, unless you have very specific reasons for doing so. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
