Jump to content

Archived

This topic is now archived and is closed to further replies.

rayeldacx

Select all records from multiple tables

Recommended Posts

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

Share this post


Link to post
Share on other sites

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);

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.