CODEWITHSUNDEEP
mysqlsql
prk
prk

Reputation: 3809

Select timestamps with different field names from two tables, and order them?

I've looked at some other questions here but can't find a solution that matches mine. I don't really understand some of the parts so I can't adapt their solutions to my problem.

I have a table like this:

post_id, post_author, post_message, post_timestamp, post_thread and.. thread_id, thread_author, thread_message, thread_timestamp and I'd like to fetch * from both (different) tables, and order by their timestamp so I could fetch the latest from both.

How can I achieve this? As I said I looked into some other solutions here but can't adapt it as the ones I can find, have the same name on their timestamp field.

Thanks in advance.

Upvotes: 0

Views: 36

Answers (1)

mbbush
mbbush

Reputation: 182

You're going to want to use a LEFT JOIN from thread to post, and you can sort using a coalesce. The performance will probably be horrible, but let's get something working first and then tune it.

SELECT p.*, t.*, coalesce(p.post_timestamp, t.thread_timestamp) as timestamp FROM thread t
LEFT JOIN (SELECT * FROM post ORDER BY post_timestamp DESC LIMIT 1) as p on p.post_thread = t.thread_id
ORDER BY timestamp DESC
LIMIT 10
;

The coalesce function takes a list of arguments and returns the first one that's not null. So in this case, it will return the post timestamp, unless there is no post, in which case it will return the thread timestamp. The join is a LEFT join so that even if the subselect returns zero results, there will still be a result row.

Upvotes: 1

Related Questions