orderby statements on multiple joins

Question

Hi,

I have three tables, each contain an auto-incrementing PK. I need to select the latest (ie, ORDERBY DESC) entries from the mix of these tables. I'd like to do it in one query, with two joins. My idea was is to somehow select a table, order it by id DESC, then somehow merge the results. Does anyone have a way (or probably a better idea) of doing this?

(using mysql) EDIT: Sorry - here's a more detailed spec: I have three tables, posts, stories, favs. Each has (at the least) three columns id, uid and date. They are both PK's. id auto-increments (separately for each table). uid is FK, but that's irrelevant. I need the result set to contain the latest 20 or 30 entries.

Answer 1

UNION with no joins would be a possability.

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

Answer 2

Since the tables have no relation, joining them doesn't make any sense, right?

As far as I understood, You would like to order records originating from 3 different tables in one result set by their creation date. There are two ways to achieve this:

1. Use a PK that is sortable and unique for all 3 tables. I know You can do this easily in PostgreSQL. In MySQL You need a workaround: Create a 4th table with an auto-increment PK and change the other 3 tables PK to a FK pointing at the 4th table PK. This is somewhat cumbersome and You need to be aware of the fact, that an auto-increment field is not a good candidate for a reliably sortable property (deleting records leads to gaps in the sequence, which might be filled later).

2. Add a column created to each of the 3 tables and store the creation date of each record. The UNION can then be sorted by this column. You already have a column date. Can't You use this column?