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:

`a.date_changed` or `a.datum_gewijzigd`, which one matches your column name? — Arjan, Jan 14 '14 at 12:48
Oh sorry, everything is in Dutch, so i changed it to English so everyone would understand better. I thought I checked and changed everything. — Deloryan, Jan 14 '14 at 12:50

Gordon Linoff · Accepted Answer · 2014-01-14T13:26:02.980

2

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;

edited Jan 14 '14 at 13:26

answered Jan 14 '14 at 12:52

Gordon Linoff

1,242,037
58
646
786

This almost works, only it takes all topics right now and not the ones from the selected subcategory, that have the given $fsubcatid. – Deloryan Jan 14 '14 at 13:02
@Deloryan . . . Fixed. When using `left join`, conditions on the first table should go in the `where` clause. Conditions on the second table should go in the `on` clause, in general. I moved the condition to the `where` clause. – Gordon Linoff Jan 14 '14 at 13:05
Hmm, it still works for all topics with the reactions. But the ones without reactions are still at the bottom. So an old topic with a reaction on the 22nd of September is higher than the topic without reactions from the 28th of September. [link]http://nl.tinypic.com/r/npnlh5/5 – Deloryan Jan 14 '14 at 13:17
I'm sorry, I guess I missed that point. I tried this and it works, thank you very much :)! – Deloryan Jan 19 '14 at 18:55

score 0 · Answer 2 · answered Jan 14 '14 at 12:59

0

If I understand the question correctly, you want to pick the MAX(a.date_changed, b.date_changed) to get the latest change by doing a LEFT join:

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

So this:

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

will pick up all NEW topics, and also any reactions in B, if there are any.

answered Jan 14 '14 at 12:59

Eugene

1,539
12
20

This is almost the same like Gordon wrote, but it still doesn't really work. I can't get it fixed, because I don't know where it goes wrong now. The ones without a comment still are at the bottom of the list, but the date is higher than the others. What I also noticed is when a topic was edited, it puts that one above the topic without the reactions (maybe something wrong with ORDER BY? ). [link]nl.tinypic.com/r/npnlh5/5 – Deloryan Jan 14 '14 at 13:25

IF ELSE statement in SQL between WHERE AND

2 Answers2