Re: Always getting back a row, even with no results

Tested
with x as
(select id,1 mark from my_table where cat = 5
union all
select null,0 mark)
select id from x where mark = (select max(mark) from x) ;

On Fri, Aug 11, 2017 at 6:57 AM, Jonathan Moules <
jonathan-lists(at)lightpear(dot)com> wrote:

> Hi List,
>
> I have a simple table:
>
> CREATE TABLE my_table
> (
> id integer NOT NULL UNIQUE,
> cat integer
> );
>
> insert into my_table (1, 2);
> insert into my_table (2, 2);
> insert into my_table (3, 3);
> insert into my_table (4, 3);
> insert into my_table (5, 2);
>
> I want do a very basic query:
> select id from my_table where cat = 3
> |id|
> |3|
> |4|
>
> This will of course return the two rows with that category. But I also
> want to be able to run the query with a non-existent cat and get a result
> of "null" for the id.
>
> select id from my_table where cat = 500
>
> would return
> |id|
> |NULL|
>
>
> now I can do that with a union all:
>
> select id from my_table where cat = 500
> union all
> select
> NULL as id
>
> |id|
> |NULL|
>
> But if I then run that query using a cat value of "3", it will not only
> return the results, but a third result, of NULL, which I don't want.
> |id|
> |3|
> |4|
> |NULL|
>
> I would like to always get a result, either of NULL, or if there are
> actual results, or those actual results without a NULL if they exist.
>
> I don't see it being possible with any of the coalesce/ifnull/case
> features, as they only action based on a single row and this can return
> multiple rows. Maybe something with the window functions or a CTE, but
> they're both new to me.
>
> Is this possible?
>
> Thanks
>