MySQL if statement returns empty or null, then set to 0-CodePudding

Using MySQL (MariaDB)

I'm looking to see if a sub-query returns null, if so I want it to be 0. I cannot get this to work. The values in this statement should return NULL (they don't exist in my database). What am I doing wrong?

SELECT 
   (COALESCE(SELECT z.ID FROM bus_values z WHERE (z.BUS_NUM=35 AND z.WORKER_ID=2922882), 0)) AS BUS_ID, 
   XVAL, 
   YVAL, 
   ZVAL, 
   DATE_ENTERED 
FROM data_points WHERE (WORKER_ID=2922882);

MySQL is telling me I have a syntax error on the line (COALESCE(SELECT ...

I've also tried to use IFNULL()

CodePudding user response：

You can do it with a LEFT join:

SELECT COALESCE(z.ID, 0) AS BUS_ID, 
       p.XVAL, 
       p.YVAL, 
       p.ZVAL, 
       p.DATE_ENTERED 
FROM data_points p LEFT JOIN bus_values z
ON z.WORKER_ID = p.WORKER_ID AND z.BUS_NUM = 35
WHERE p.WORKER_ID = 2922882;

CodePudding user response：

From your query, you can try to use CROSS JOIN

you can't use a subquery in COALESCE function, because there may be multiple rows in the result set.

SELECT 
   COALESCE(z.ID, 0) AS BUS_ID, 
   p.XVAL, 
   p.YVAL, 
   p.ZVAL, 
   p.DATE_ENTERED 
FROM data_points p
CROSS JOIN bus_values z
WHERE (p.WORKER_ID=2922882) AND z.BUS_NUM=35 AND z.WORKER_ID=2922882;

otherwise, you can try to use limit 1 in the subquery end.

SELECT 
   COALESCE((SELECT z.ID FROM bus_values z WHERE z.BUS_NUM=35 AND z.WORKER_ID=2922882 limit 1), 0) AS BUS_ID, 
   XVAL, 
   YVAL, 
   ZVAL, 
   DATE_ENTERED 
FROM data_points WHERE (WORKER_ID=2922882);

CodePudding user response：

As pointed out elsewhere, using a subquery in an expression like this only works if it returns at most one row (which if necessary you can enforce with LIMIT 1).

Your syntax error is caused by you needing two sets of parentheses and only having one. A subquery in an expression must be in parentheses, and the coalesce call needs parentheses. So simply changing:

SELECT 
   (COALESCE(SELECT z.ID FROM bus_values z WHERE (z.BUS_NUM=35 AND z.WORKER_ID=2922882), 0)) AS BUS_ID,

to:

SELECT 
   (COALESCE((SELECT z.ID FROM bus_values z WHERE (z.BUS_NUM=35 AND z.WORKER_ID=2922882)), 0)) AS BUS_ID,

fixes your problem, but if bus_num and worker_id will not identify a unique row, you should select MAX(z.ID) or add an order by and limit 1 to the subquery to get whichever id it is you want found.