Forum sorting by last reply posted with two tables

Databases
1

user
forum
subforum

discussions are tied to one specific projects so we don’t need categories

user {userid, username, password, displayname, active, email, deleted}
forum{forumid, projectid, userid, title, body, createdate, deleted, admincomment}
subforum {subforumid, forumid, userid, projectid, body, createdate, deleted}

forum(topic) was getting displayed with latest forum at the top. when user clicked on forum title it opens new page with forum title on top and subforums(replies) under it.

on main page we displaying forum as table
topic|created by and datetime of creation| replies | last reply

i wanted to dsiplay topic sorted according to the last reply posted but faced aproblem

problem is that when i join 3 tables main forum with no replies did not get displayed but it get sorted by last reply posted
sql
SELECT f.forumid, f.userid, f.title, f.body, u.displayname, f.createdate, s.createdate as replydate FROM forum f INNER JOIN users u ON f.userid = u.userid join (select * from subforum where deleted=0 order by createdate desc) as s on s.forumid=f.forumid WHERE f.projectid = 220 AND f.deleted=0 and s.deleted =0 group by f.forumid ORDER BY replydate desc;

problem is with database design. i have always seen posts as one table and reply to posts in same table.

database was designed like this before i joined the project.
is there a way to achieve the result i wanted with this design?

thanks

2 
SELECT f.forumid
     , f.userid
     , f.title
     , f.body
     , u.displayname
     , f.createdate
     , [COLOR="Blue"]COALESCE(s.createdate,f.createdate)[/COLOR] AS replydate 
  FROM forum AS f 
INNER 
  JOIN users AS u 
    ON u.userid = f.userid 
[COLOR="blue"]LEFT OUTER[/COLOR]
  JOIN subforum AS s
    ON s.forumid = f.forumid
   AND s.deleted = 0 
 WHERE f.projectid = 220 
   AND f.deleted=0 
ORDER 
    BY replydate DESC;

:cool:

3

Thank you very much.

you saved my day. learned something new.

i think i should give another good reading to joins.
this is the first time i came across COALESCE also.

thanks again.