Thread: [SQL] Always getting back a row, even with no results

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

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

with x as 

(select id,1 mark from my_table where cat = 3

union all

select null,0 mark)

select id from x where mark = (select max(mark) from x) alia;

 

Don't have SQL right now so can't test it.

On Fri, Aug 11, 2017 at 8:43 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <jonathan-lists@lightpear.com> wrote:

Hi David,
I'm afraid I don't really understand this response (I've not done much with arrays), but it doesn't seem to work for my purpose.

No NULL is returned if there is no result (i.e. cat = 50); instead, there's simply no rows.

What aspect of Arrays is this trying to take advantage of?

​My bad, I had tested the "false" version with a single record, without the array, and it indeed works.  But the scalar subselect prevents the inner query from returning more than one row.  I added the array to handle the multiple rows setup (which required testing the true path) and forgot to go back and test the false path.

The idea of the array was to keep the inner subquery scalar.

The following works on 9.5 - not positive whether it will on 10 though, we made some changes in this area.

SELECT 

unnest(

COALESCE(

(SELECT array_agg(col) FROM ( VALUES (1), (2) ) vals (col) WHERE true),

 ARRAY[null]::int[]

)

);

David J.

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