mysql - Use fields from second table if entry already exists -

i'm working on forum right , try create overview latest reply of each threat if exists.

i got 2 tables, 1 first thread , 1 replies.

table1 (threads)

id, board, title, text, created...

table2 (replies)

id, board, thread, title, text, created...

now i'm trying select each thread , if exist "created" field latest reply instead.

so have like:

select a.id, a.id thread, a.title, a.created if entry in b b.thread = a.id use latest b.created 

thanks help!

here solution (thanks lukled)

select  a.id,  a.title,  coalesce(b.created, a.created) created,  coalesce(b.author, a.author) author  forum_threads  left join (select thread, max(id) id forum_replies group thread) c on c.thread = a.id  left join forum_replies b on b.id = c.id  a.board = '".data::escape($id)."'  order created desc 

try (using subselect):

select    t.id,    t.board,   t.title,   t.created,   (select max(created) replies r r.thread = t.id) last_reply_date,   coalesce((select max(created) replies r r.thread = t.id), t.created) last_activity_date   threads t 

for bigger queries faster:

select    t.id,   t.board,   t.title,   t.created,   rg.created last_reply_date,   coalesce(rg.created, t.created) last_activity_date threads t  left join (select thread, max(created) created replies r group thread) rg on rg.thread = t.id 

edit:

if want retrieve more 1 field related table, won't easy:

select    t.id,   t.board,   t.title,   t.created,   r.created last_reply_date,   coalesce(r.created, t.created) last_activity_date,   r.author last_reply_author threads t  left join (select thread, max(id) last_reply_id replies group thread) rg on rg.thread = t.id left join replies r on r.id = rg.last_reply_id 

this select:

select thread, max(id) last_reply_id replies group thread 

is responsible creating list of last replies thread. assume if reply has highest id, latest.

so in query join threads table select, contains ids of last replies, , replies table.