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.