Use one if with two selects

Question

I am doing a query, in which the second query can give "NULL" or a value (if there is data).

SELECT *, (SELECT tb1.image FROM ce_users_images tb1
           WHERE tb1.iduser = users.iduser LIMIT 1) AS tmp1
FROM ce_users users
WHERE users.id_user=4
LIMIT 1

I intend that if you return NULL in this second query, return me a default value, otherwise it gives the query value.

I tried to use an IFNULL, but I was always wrong. You can help perform this in the query??

Answer 1

You can use the function IFFNULL or COALESCE:

SELECT 
    users.*,
    COALESCE(
    (SELECT 
         tb1.image 
     FROM ce_users_images tb1
     WHERE tb1.iduser = users.id_user
     LIMIT 1),'Nenhuma imagem informada') AS tmp1
FROM ce_users users
WHERE users.id_user=4
LIMIT 1

a little more about the coalesce function: http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php

Answer 2

As you want to return a field from a related table you can use JOIN to do this. In this case you need to return table content ce_users, regardless of whether there is a related record in the table of ce_users_images LEFT JOIN would work very well.

SELECT 
    users.*, IFNULL(images.image,'img_default.jpg')
FROM
    ce_users AS users
        LEFT JOIN
    ce_users_images AS images ON users.iduser = images.iduser
WHERE
    users.id_user = 4

If there is more than one related record in the table you can use the GROUP BY clauses or add filters in the WHERE or ON clauses.

Mysql JOIN