IF ELSE in SQL between WHERE AND

advertisements

I have searched hours for this, but nothing seems to be working till now.

I am trying to order my forum topics by last activity. So I'm searching in the topics and reactions for the last activity. All seems to be working, untill a new topic gets added.

And this is my current code, and yes I understand why it doesn't work, but I really can't seem to get it fixed.

SELECT a.forum_topic_id, a.title, a.sticky, a.date_changed, b.date_changed
FROM forum_topics AS a, forum_reactions AS b
WHERE a.forum_subcat_id = ".$fsubcatid."
AND (a.forum_subcat_id = ".$fsubcatid." AND a.forum_topic_id = b.forum_topic_id)
OR (a.forum_subcat_id = ".$fsubcatid.")
ORDER BY
CASE WHEN a.date_changed < b.date_changed THEN b.date_changed WHEN a.date_changed > b.date_changed THEN a.date_changed END
DESC;");

So as you can see. a.forum_subcat_id can NOT be b.forum_topic_id when a new topic is made, because there are no reactions yet. And that's where it goes wrong. For all topics with reactions he has to look at this:

WHERE a.forum_subcat_id = ".$fsubcatid." AND a.forum_topic_id = b.forum_topic_id ORDER BY ...

For all NEW topics, he has to look at only this:

WHERE a.forum_subcat_id = ".$fsubcatid." ORDER BY...

And in the frontend part I pick it up with a foreach loop PHP. So for each topic, he has to search these things.

I have gone through these links till now:


I think you want a left outer join. In general, it is better to use explicit join syntax:

SELECT a.forum_topic_id, a.title, a.sticky, a.date_changed, b.date_changed
FROM forum_topics AS a left outer join
     forum_reactions AS b
     on a.forum_topic_id = b.forum_topic_id
WHERE a.forum_subcat_id = '".$fsubcatid."'
ORDER BY CASE WHEN a.datum_gewijzigd < b.datum_gewijzigd THEN b.datum_gewijzigd
              WHEN a.datum_gewijzigd > b.datum_gewijzigd THEN a.datum_gewijzigd
         END DESC;

Note that the order by does not take a non-match into account. You might want a condition where b.datum_gewijzigd is null for this situation.

EDIT:

As I mentioned in the note, you need to modify the order by to get things with no reactions first. I think this may be what you want:

ORDER BY CASE WHEN a.datum_gewijzigd < b.datum_gewijzigd THEN b.datum_gewijzigd
              WHEN a.datum_gewijzigd > b.datum_gewijzigd THEN a.datum_gewijzigd
              ELSE a.datum_gewijzigd
         END DESC;