RE: select data from two tables without join
it depends on how you want it if you want them consecutively, you probably want to UNION two queries together, but you need to make sure that the column types are identical... i.e. select meeting_name as event_name, meet_date as event_date from meetings where meet_date = curdate() union select train_name as event_name, train_date as event_date from trainings where train_date = curdate() order by event_date -Original Message- From: Tony Thomas [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 9:01 AM To: [EMAIL PROTECTED] Subject: select data from two tables without join I have two tables that are similar, but not related. One is for meetings and the other for training. I'd like to run a query to select data from both based on the date so I can display the information on a web page. Is that possible? It seems unnecessary to run a separate query for each. I'd like to do something like this: SELECT * FROM meetings, trainings WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) ORDER BY meet_date DESC, train_date DESC; But this doesn't work. Is something like this possible? -- 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: select data from two tables without join
If the tables are *identical* in structure ant are MyISAM, you can create a MERGE table from them. See http://www.mysql.com/doc/en/MERGE.html Alec - I have two tables that are similar, but not related. One is for meetings and the other for training. I'd like to run a query to select data from both based on the date so I can display the information on a web page. Is that possible? It seems unnecessary to run a separate query for each. I'd like to do something like this: SELECT * FROM meetings, trainings WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) ORDER BY meet_date DESC, train_date DESC; But this doesn't work. Is something like this possible? -- 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: select data from two tables without join
Tony Thomas [EMAIL PROTECTED] wrote: I have two tables that are similar, but not related. One is for meetings and the other for training. I'd like to run a query to select data from both based on the date so I can display the information on a web page. Is that possible? It seems unnecessary to run a separate query for each. I'd like to do something like this: SELECT * FROM meetings, trainings WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) ORDER BY meet_date DESC, train_date DESC; It gives you Cartesian product of two tables. But this doesn't work. Is something like this possible? Probably you need UNION: http://www.mysql.com/doc/en/UNION.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select data from two tables without join
If the tables are the same, then maybe you could use one table with a field that tells the application if its a meeting or training. Other then that, UNION is very cool. As long as you have the same field definitions in 2 or more queries they can be added togeather. Unfortunatly you can do sub selects in 4.0.1 :-( so the list will always come out with the first table at the top, the second following. On a non-MySQL point, if you do select using a union but want every thing to be sorted as if it was one table then you can always use an array to store the records and sort that. It works really well with PHP. This is also a solution if you are using MySQL 3.x as that dosn't support UNION. Haydies. - Original Message - From: Tony Thomas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 2:01 PM Subject: select data from two tables without join : I have two tables that are similar, but not related. One is for : meetings and the other for training. I'd like to run a query to select : data from both based on the date so I can display the information on a : web page. Is that possible? It seems unnecessary to run a separate : query for each. I'd like to do something like this: : : SELECT * FROM meetings, trainings : WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) : ORDER BY meet_date DESC, train_date DESC; : : But this doesn't work. Is something like this possible? : : : -- : 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: select data from two tables without join
I'm using 3.23.56, so UNION is out. MERGE looks like it might do the trick. The tables are duplicate other than the field names. The structure is identical. On Tuesday, September 16, 2003, at 08:32 AM, Haydies wrote: If the tables are the same, then maybe you could use one table with a field that tells the application if its a meeting or training. Other then that, UNION is very cool. As long as you have the same field definitions in 2 or more queries they can be added togeather. Unfortunatly you can do sub selects in 4.0.1 :-( so the list will always come out with the first table at the top, the second following. On a non-MySQL point, if you do select using a union but want every thing to be sorted as if it was one table then you can always use an array to store the records and sort that. It works really well with PHP. This is also a solution if you are using MySQL 3.x as that dosn't support UNION. Haydies. - Original Message - From: Tony Thomas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 2:01 PM Subject: select data from two tables without join : I have two tables that are similar, but not related. One is for : meetings and the other for training. I'd like to run a query to select : data from both based on the date so I can display the information on a : web page. Is that possible? It seems unnecessary to run a separate : query for each. I'd like to do something like this: : : SELECT * FROM meetings, trainings : WHERE (meet_date = CURDATE()) AND (train_date = CURDATE()) : ORDER BY meet_date DESC, train_date DESC; : : But this doesn't work. Is something like this possible? : : : -- : 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: select data from two tables without join
If you use merge you might not be able to tell which records came from which table. If that dosn't matter obviously its not a problem. - Original Message - From: Tony Thomas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 2:48 PM Subject: Re: select data from two tables without join : I'm using 3.23.56, so UNION is out. MERGE looks like it might do the : trick. The tables are duplicate other than the field names. The : structure is identical. : : On Tuesday, September 16, 2003, at 08:32 AM, Haydies wrote: : : If the tables are the same, then maybe you could use one table with a : field : that tells the application if its a meeting or training. : : Other then that, UNION is very cool. As long as you have the same field : definitions in 2 or more queries they can be added togeather. : Unfortunatly : you can do sub selects in 4.0.1 :-( so the list will always come out : with : the first table at the top, the second following. : : On a non-MySQL point, if you do select using a union but want every : thing to : be sorted as if it was one table then you can always use an array to : store : the records and sort that. It works really well with PHP. This is also : a : solution if you are using MySQL 3.x as that dosn't support UNION. : : Haydies. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select data from two tables without join
My fields have unique names. For instance, the fields for the date in each table are meet_date and train_date for the meetings and trainings tables respectively. If I did a merge, would I still be able to separate them out with PHP? For instance: while ($row=mysql_fetch_array($result)) { echo $row[meet_date] . br /\n . $row[training_date] . br /\n; } Or do field names need to be identical too (thus eliminating any chance of keeping the data separate)? On Tuesday, September 16, 2003, at 09:42 AM, Haydies wrote: If you use merge you might not be able to tell which records came from which table. If that dosn't matter obviously its not a problem. - Original Message - From: Tony Thomas [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 16, 2003 2:48 PM Subject: Re: select data from two tables without join : I'm using 3.23.56, so UNION is out. MERGE looks like it might do the : trick. The tables are duplicate other than the field names. The : structure is identical. : : On Tuesday, September 16, 2003, at 08:32 AM, Haydies wrote: : : If the tables are the same, then maybe you could use one table with a : field : that tells the application if its a meeting or training. : : Other then that, UNION is very cool. As long as you have the same field : definitions in 2 or more queries they can be added togeather. : Unfortunatly : you can do sub selects in 4.0.1 :-( so the list will always come out : with : the first table at the top, the second following. : : On a non-MySQL point, if you do select using a union but want every : thing to : be sorted as if it was one table then you can always use an array to : store : the records and sort that. It works really well with PHP. This is also : a : solution if you are using MySQL 3.x as that dosn't support UNION. : : Haydies. -- 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]