mysql if return false if no record found

Question

a typical scenario of user and products, I'd like the query to return 'false' if user is not assigned any product but it returns empty.

serviceRequest.sql = "SET @productId = ?;\
     SELECT IF(product.productId = @productId, 'true', 'false') AS entitled FROM user_token \
     INNER JOIN user_product ON user_token.user_id = user_product.user_id \
     INNER JOIN product ON user_product.product_id = product.id AND authToken = ? AND product.productId = @productId;";

serviceRequest.values = [request.query.productId, request.query.authToken];

Answer 1

Your where criteria is making it return empty instead of false. This should work though using outer joins instead:

SELECT IF(ut.user_id IS NOT NULL, 'true', 'false') AS entitled 
FROM product p 
     LEFT JOIN user_product up ON p.id = up.product_id
     LEFT JOIN user_token ut ON up.user_id = ut.user_id 
                            AND ut.authToken = ?
WHERE p.productId = @productId;