Complex SQL statement forum topic listing -

Complex SQL statement forum topic listing -

i've been attempting several hours no luck, wondered if sql expert set me out of misery.

the statement main forum page lists topics, ordered lastly posted in. have 2 tables forum_topics , forum_replies

forum_topics has fields id, name, date forum_replies has id, topic_id, name, date, del

here's i've got far, lines beingness psuedo code. i'm having problem because i'm trying order entire forum_topics listing lastly date found in forum_replies. you'll i'm trying do;

select ft.*, fr.date lastpost_date forum_topics ft left bring together forum_replies fr on fr.topic_id=ft.id (fr.del=0 or fr.account_id=5) <-- need these conditions order fr.date desc limit 20

this doesnt want, advice?

use subselect latest activity in given topic:

select ft.*, fr.lastpost_date forum_topics ft inner bring together ( select fr.topic_id, max(fr.date) lastpost_date forum_replies fr (fr.del = 0 or fr.account_id = 5) grouping fr.topic_id ) fr on fr.topic_id = ft.id order fr.lastpost_date limit 20

the subquery filters posts have been deleted , sent ordinary users , lists latest date each topic. in order list topics have replies, bring together type set inner.

sql