Joining 2 tables and ordering by time

[Chapati]

Hey guys. I want to join 2 tables together and then ordering the new table by time. It will be used as a news feed for individual users when actions are taken upon them. One is a battlelog (they will be alerted when they are attacked) and another is a bank transferlog (they will be alert when someone transfers them money), and I want to show the 10 latest items. This is what I WANT to use:

$result=mysql_query("SELECT * FROM battlelog WHERE defender=$id ORDER BY time DESC LIMIT 0,10 inner join transferlog WHERE recipient=$id ORDER BY time DESC LIMIT 0,10");

The resulting table should look something like this:

attacker | defender | time | recipient | sender | money
241 ------- 17------ 9:30
--------------------- 8:41 ---17 ----- 8564 -----1
---------------------7:48 ----17 -----47285----- 8000
8576 ------ 17 ------- 6:00

Any ideas? Thanks in advance.

[Fumigator]

I think a UNION will be more appropriate for what you want, because the JOIN query you gave as what you need doesn't actually join the two tables together. UNION will select data from each table and return them in the same resultset. Using ORDER BY on the union query will sort them by date.

http://dev.mysql.com/doc/refman/5.0/en/union.html

[Chapati]

Thanks a bunch, UNION does just what I was looking for, along with a bit of tweaking.