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

On 11/08/2017 16:57, Jonathan Moules 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.
Do smth like along the lines (might want to add additional code for ordering, this just happens to run correctly) :
select * from my_table where cat = 500 UNION select null,null LIMIT CASE WHEN (select count(*) from my_table where cat=500)>0 THEN (select count(*) from my_table where cat=500) ELSE 1 END ;
Of course you can write a function to do that for you, but what made you want this in the first place? Maybe this is bad design ?

>
> Is this possible?
>
> Thanks

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt