Re: Select CASE when null ?

On Wed, Jan 14, 2009 at 05:35:55PM -0300, Camilo Sperberg wrote:
> SELECT
>   CASE mid WHEN NULL THEN CAST(0 AS integer)
>            ELSE mid
>   END AS mid,
>   CASE id_group WHEN NULL THEN CAST(0 AS integer)
>                 ELSE id_group
>   END AS id_group
> FROM users
> WHERE username = 'test';
>
> This query returns:
> mid --- id_group
> 1   ---    2
>
> Now, what I want is when the user isn't found, (aka WHERE username isn't
> found) it should return me this:
>
> mid --- id_group
> 0   ---    0

SELECT COALESCE(mid,0) AS mid, COALESCE(id_group,0) AS id_group
FROM users
WHERE username = 'test'
UNION
SELECT 0, 0
WHERE NOT EXISTS (SELECT 1 FROM users WHERE username = 'test');


--
Mark
http://www.lambic.co.uk