Sort data from 2 tables ?!?!

magic123

Hi,

i have a problem with sorting data from 2 tables:

tableA id time text
1 10:10 welcome
2 10:14 welcome2

tableB id time text
1 10:12 bye
2 10:15 bye2

How can i set a mysql command that order by time form tableA and tableB at the same time.

select * from tableA, tableB order by time;

doesn't work!

solution
1 10:10 welcome
1 10:12 bye
2 10:14 welcome2
2 10:15 bye2

thx 4 help

[deleted]

A join (SELECT * FORM A,😎 is meant to somehow link the data from the two tables.
If you want to fetch all records from A, then all from B and sort the results, you'll have to put both results into an array and sort the array.
You could also use a temporary table to store the results, but that gets messy in a multi user environment.

Anon

In a database supporting UNIONs and subselects, the query would be:

select from (select from t1 UNION select * from t2) order by mydatecolumn;

Look for doing multi-step procedures with less capable databases such as MySQL. Temporary tables and sorting in code are two ways.

Anon

If I assume that id increases with increasing time, and that the A & B ids are paired, then this may work:

select * from A, B where A.id = B.id

If I assume that A & B ids are paired, but that id does not necessarily increase with time, then this might work:

select * from A, B where A.id = B.id order by A.time

If you're trying to get what you have labeled as "solution", then assuming that the entries are always in ascending order, this does it:

select * from A, B order by A.time

Hope this helps

jcleary

I think the only way to do this is in code. You could create two queries and then compare them on the fly. Thats if the array option is not possible (i.e. the tables are too large).

qry_1 = mysql_query("select from tableA order by time");
qry_2 = mysql_query("select from tableB order by time");

$row_1 = mysql_fetch_array($qry_1);
$row_2 = mysql_fetch_array($qry_1);

while ($row_1 || $row_2) {
if ($row_1["time"] <= $row_2["time"]) {
echo $row_1["id"] . $row_1["time"] ;
$row_1 = mysql_fetch_array($qry_1);
} else {
echo $row_2["id"] . $row_2["time"] ;
$row_2 = mysql_fetch_array($qry_2);
}
}

I haven't check this code, but yo get the idea.

kparker

I was going to mention UNION, but Gerald beat me to it. Meanwhile, assuming you can't move to a more capable dbms...

You could also use a temporary table to store the results,
but that gets messy in a multi user environment.

Not at all--at least mysql starting with 3.23 correctly supports a 'create temporary table' syntax where the table (and the name) is per-connection. (So do current and previous versions of PostgreSQL, fwiw.)

magic123

thx 4 help, but i still have no sucess.

it seams like the union command is no supported by my mysql database.

The Problem:
""""""""""""

Table info1
id title time
1 item1 200103051011
2 item2 200104050916
3 item3 200105041102

Table info2
id title time
1 stuff1 200101051011
2 stuff2 200104051216
3 stuff3 200106051102

Now with php i want to get the following output (both tables sort by time):

1 stuff1 200101051011
1 item1 200103051011
2 item2 200104050916
3 item3 200104051102
2 stuff2 200104051216
3 stuff3 200106051102

There are Informations (grouped) in the tables info[1-6] and i want to display the last(newst) 10 info's from all tables order by time.

i hope this explains my problem better.

thx 4 any help

Anon

To do this with MySQL, create a temporary table, dump contents of both tables into it, and select from it.

magic123

How can i dump the contents of 2 tables in 1 virutal table with mysql ?

thx

Anon

create table t1 (a int, b int);
create table t2 (a int, b int);

<< fill in both tables >>

create table uniquetablename (a int, b int);
select into uniquetablename select from t1;
select into uniquetablename select from t2;

Try it. I haven't used MySQL since I discovered PostgreSQL, and don't have a database to test against.