IF ELSE statement in SQL between WHERE AND

Question

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:

• http://sqltutorials.blogspot.nl/2007/06/sql-ifelse-statement.html
• http://www.w3schools.com/sql/sql_isnull.asp
• IF ELSE Statement in SQL
• SQL : a condition (IF-ELSE) in INSERT INTO
• IF Condition in an SQL query

Answer 1

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;