Jump to content


Photo

Select all records from multiple tables


  • Please log in to reply
2 replies to this topic

#1 rayeldacx

rayeldacx
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 30 October 2003 - 06:53 AM

Hi! I\'m a newbie in php/mysql....

I just want to ask how can I select all the data from different tables (table_a, table_b, table_c, table_d...etc) using only one query. All the fields in the tables are the same (ID, Lname, FName, DatePosted) and I want to output the result by DatePosted (example all data with DatePosted 2003-10-30 will be displayed).

Thanks in advance. I really need help.


DacX

#2 GeoffOs

GeoffOs
  • Members
  • PipPip
  • Member
  • 24 posts
  • LocationCheshire, England

Posted 30 October 2003 - 08:25 PM

Try the union clause. If all the tables have the same definition then you can add mulitple selects together to become one recordset:
SELECT ...UNION [ALL]SELECT ...  [UNION   SELECT ...]
Have a look at the mySQL Help here : http://www.mysql.com/doc/en/UNION.html

NOTE, this is implemented in MySQL 4.0.0


Though somebody posted on the mysql web site this work around if you are working on mysql 3....
An alternative, rather simpler (especially with very complex select statements) way to \'use union\' in 3.x might be the following:Build a nice union query. (save it somewhere, so you can use that if you upgrade)If you would say that query was \'(*cool_select_statement_1*) UNION (*cool_select_statement_2*) *order_and_group_by_stuff*\'.You could make an replacement set of query\'s like this:CREATE TEMPORARY TABLE temp_union TYPE=HEAP *cool_select_statement_1*;INSERT INTO temp_union *cool_select_statement_2*;SELECT * FROM temp_union *order_and_group_by_stuff*;DROP TABLE temp_union;Note that I\'ve use a HEAP and TEMPORARY table because that combination is rather fast and, well, temporary.You can\'t execute these query\'s on one line (well I coudn\'t), so it would look like this in PHP:mysql_query(\'CREATE..\', $connection);mysql_query(\'INSERT..\', $connection);$query = mysql_query(\'SELECT..\', $connection);mysql_query(\'DROP..\', $connection);

Beyond a critical point within a finite space, freedom diminishes as numbers increase....[br]The human question is not how many can possibly survive within the system, but what kind of existence is possible for those who do survive."[br]-- Frank Herbert - Dune

#3 rayeldacx

rayeldacx
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 03 November 2003 - 06:04 AM

thanks man...

im using mysql 3.23.53 so what I did was to upgrade it to 4.0.15
so I can use UNION clause but I got another problem in running the new mysql version. I\'ll just post another topic.

thanks again

DacX




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users