How can I get a SQL query to show one value if another value is empty/not existent

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.

`ISNULL` only takes 1 argument and returns a boolean; you might want `IFNULL(a,b)` (see [this question](http://stackoverflow.com/questions/3215454/mysql-ifnull-else)). — Kenney, Jan 31 '16 at 13:30
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). Fix the query and explain what you are trying to do. — Gordon Linoff, Jan 31 '16 at 13:33

score 0 · Answer 1 · answered Jan 31 '16 at 13:36

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.

What I want is that if there are no results in `threadpost` that it takes the result of `thread` and if there are results in `threadpost` that it takes that as `idFix`. Please don't remove the `SELECT *`, I have that there for a reason. — Tom, Jan 31 '16 at 13:43
Also, the `threadCount` and the `postCount` work fine without adding what you did. The query works fine without the problem I stated in OP. — Tom, Jan 31 '16 at 13:45

How can I get a SQL query to show one value if another value is empty/not existent

1 Answers1