如果另一个值为空/不存在，如何获取SQL查询以显示一个值

Question

I currently have a query and it gives me an error when I run it, the error is in this part:

CASE ISNULL(po.u_id, "") WHEN "" THEN th.u_id ELSE po.u_id END AS idFix

The whole query:

SELECT *, count(th.t_id) AS threadCount, count(po.p_id) as postCount, CASE ISNULL(po.u_id, "") WHEN "" THEN th.u_id ELSE po.u_id END AS idFix, CASE WHEN th.postdate > IFNULL(po.postdate, "0000-01-01 00:00:00") THEN max(th.postdate) ELSE max(po.postdate) END AS lastdate FROM thread th LEFT OUTER JOIN threadpost po ON th.t_id = po.t_id WHERE th.sc_id =' . $fetch['sc_id'] . ' AND th.archived = 0

The $fetch['sc_id'] part works fine and is a numeric value.

The error I get is:

Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function 'ISNULL' in C:\xampp\htdocs\goatbb\pages\home.php:39 Stack trace: #0 C:\xampp\htdocs\goatbb\pages\home.php(39): PDO->query('SELECT *, count...') #1 C:\xampp\htdocs\goatbb\index.php(72): include('C:\\xampp\\htdocs...')

What I want is when I execute this query idFix returns with the user id that posted last, so the same principle with the lastdate, that part of the query works fine, but now it also needs to work with the user id instead of the date.

I already searched on SO, but for some reason when I tried the solutions of those questions it didn't work for me.

---

I edited the query slightly, I changed the not working part to this:

CASE WHEN IFNULL(po.u_id, 0) THEN po.u_id ELSE th.u_id END AS idFix

The problem I have now is that when I post something it works, but when I react on a post it will always take that value.

So now I need to check what was inserted last in the database, I think I have to do something similar as with the lastdate part, but I am not sure how to do that.

Answer 1

Your query has numerous problems. You are doing an aggregation and then using SELECT *. This is really bad practice, and essentially meaningless because your query only returns one row (and there are presumably multiple rows).

In addition, the first two counts are very likely to return the same value. I am guessing you want COUNT(DISTINCT).

From your description, I would expect the query to look more like this simple aggregation:

SELECT COUNT(DISTINCT th.t_id) AS threadCount,
       COUNT(DISTINCT po.p_id) as postCount,
       MAX(GREATEST(po.u_id, th.u_id)) as idFix, 
       MAX(po.postdate) lastdate
FROM thread th LEFT OUTER JOIN
     threadpost po
     ON th.t_id = po.t_i
 WHERE th.sc_id =' . $fetch['sc_id'] . ' AND th.archived = 0;

However, I'm not sure this is the exactly logic you want, because that is not explained very well.