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