Re: Data from two tables in one query
Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 4:01 PM Subject: Data from two tables in one query We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- 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: Data from two tables in one query
Two points about the messages below: - With respect to the suggestion to use UNION: Original poster is using 3.23, so UNION cannot be used (it's implemented in MySQL 4.0) - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. So it may be the case that a MERGE table will solve the problem. At 16:31 +0200 10/14/03, Rory McKinley wrote: Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 4:01 PM Subject: Data from two tables in one query We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
Paul, - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. Very true, I must have misread. Now my question is, when I create a merge table, do I have to include every field from the two tables I'm merging or can I just grab the ones I want? Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 10:54 AM To: Rory McKinley; Jeff McKeon; [EMAIL PROTECTED] Subject: Re: Data from two tables in one query Two points about the messages below: - With respect to the suggestion to use UNION: Original poster is using 3.23, so UNION cannot be used (it's implemented in MySQL 4.0) - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. So it may be the case that a MERGE table will solve the problem. At 16:31 +0200 10/14/03, Rory McKinley wrote: Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 4:01 PM Subject: Data from two tables in one query We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
At 11:14 -0400 10/14/03, Jeff McKeon wrote: Paul, - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. Very true, I must have misread. Now my question is, when I create a merge table, do I have to include every field from the two tables I'm merging or can I just grab the ones I want? The former. http://www.mysql.com/doc/en/MERGE.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data from two tables in one query
union isnt available with 3.23 either Nitin - Original Message - From: Rory McKinley [EMAIL PROTECTED] To: Jeff McKeon [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 8:01 PM Subject: Re: Data from two tables in one query Hi Jeff Have you looked at UNIONS? An example would be something as follows: SELECT field_1, field_2, field_3 FROM table 1 WHERE blah blah blah UNION SELECT field_1, field_2, field_3 FROM table 1_old WHERE blah blah blah ORDER BY field_1 This should do the trick... Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) - Original Message - From: Jeff McKeon [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 4:01 PM Subject: Data from two tables in one query We're currently running mysql ver 3.23 with plans to upgrade to 4.x soon but we're not ready yet. I have two tables that are identical, table1 and table1_old. Table1_old contains all data writen to table1 prior to july of 2003. I need to do a single query that pulls the same fields from both tables and output's them in the correct order. Is this possible? I'm outputing this data to PHP web page. I know in ver 4.x there is table called a merge table for this type of thing but we're stuck with 3.23 for now. Jeff -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 11:19 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Data from two tables in one query At 11:14 -0400 10/14/03, Jeff McKeon wrote: Paul, - Original poster suggests that MERGE tables are not implemented until 4.0, but this is incorrect. MERGE tables are available as of MySQL 3.23.25. Very true, I must have misread. Now my question is, when I create a merge table, do I have to include every field from the two tables I'm merging or can I just grab the ones I want? The former. http://www.mysql.com/doc/en/MERGE.html -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
At 11:50 -0400 10/14/03, Jeff McKeon wrote: Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff Hmmm... % perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Can you post the results for SHOW CREATE TABLE for the tables you're trying to merge? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
, Status tinytext, StatusDesc text, DontCharge tinyint(4), PendingTo tinyint(4), SentTo tinytext, FromAddress text, ToAddress tinytext, FromName text, ToName text, Size INT(11), CompressedSize INT(11), Type tinytext, Priority tinyint(4), ReadFlagSent int(11), Accounting int(11), QRG tinytext, Modulation tinytext, RetryCount tinyint(4), LastTry int(11), RemoteFaxID tinytext, LLCharge tinytext, LLTime int(11), LLPages int(11), ExternalSerial text, GPS tinytext, Price Double, User_ID int(11),Pay_ID int(11), Tarif_ID int(11), CopyOf int(11), ShipID int(11), key(ID)) Type=MERGE UNION=(Mailbox_Old,Mailbox); Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 11:59 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Data from two tables in one query At 11:50 -0400 10/14/03, Jeff McKeon wrote: Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff Hmmm... % perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Can you post the results for SHOW CREATE TABLE for the tables you're trying to merge? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Data from two tables in one query
Never mind, I used the show create table Mailbox results as my create table supermailbox query and it worked. Thanks Jeff -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 14, 2003 11:59 AM To: Jeff McKeon; [EMAIL PROTECTED] Subject: RE: Data from two tables in one query At 11:50 -0400 10/14/03, Jeff McKeon wrote: Ok, created the merge table, now I get this error when I log into the mysql server [snip] Didn't find any fields in table 'SuperMailbox' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3903 to server version: 3.23.56 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show columns from SuperMailbox; ERROR 1016: Can't open file: 'SuperMailbox.MRG'. (errno: 143) [snip] Any ideas what I've screwed up now? Thanks, Jeff Hmmm... % perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Can you post the results for SHOW CREATE TABLE for the tables you're trying to merge? -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
