Geek Answers Handbook

IF ELSE in SQL between WHERE AND

I was looking for a watch for this, but still nothing works.

I am trying to order the topics of my forum for the latest activity. Therefore, I am looking for topics and reactions for the latest activity. Everything seems to work until a new theme is added.

And this is my current code, and yes, I understand why it does not work, but I really can not fix it.

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 CANNOT be b.forum_topic_id when a new topic is created, because there is no reaction so far. And this is where everything goes wrong. For all topics with reactions, he should 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 should only look at this:

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

And in the front-end, I pick it up using the foreach PHP loop. Therefore, for each topic, he must look for these things.

I still survived these links:

+4
2

, left outer join. join:

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;

, order by . where b.datum_gewijzigd is null.

EDIT:

, order by, no. , , :

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;
+2

, MAX (a.date_changed, b.date_changed), , LEFT join:

FROM forum_topics AS a 
LEFT JOIN forum_reactions AS b ON a.forum_topic_id = b.forum_topic_id

, :

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

, B, .

0

More articles:


All Articles