interphx interphx - 6 months ago 10
SQL Question

How do I make aggregate query return empty set instead of NULL row?

I have a SQL query like this:

SELECT t1.name, MAX(t2.value)
FROM t2
JOIN t1 ON t1.id = t2.t1_id
WHERE t2.t1_id = 1 AND t2.text_id = 16;


However, when t2 selection is empty, it returns a row containing NULL values (because of MAX function returning NULL when called on an empty set). I would like it to return an empty set instead. How can I achieve it?

Answer

Try this in sql server ...

with cte as
(
SELECT t1.name, MAX(t2.value) a
FROM t2
JOIN t1 ON t1.id = t2.t1_id
WHERE t2.t1_id = 1 AND t2.text_id = 16;
)
select * from cte where a is not null

try this in Mysql

  select p.* from 
(
    SELECT t1.name, MAX(t2.value) a
    FROM t2
    JOIN t1 ON t1.id = t2.t1_id
    WHERE t2.t1_id = 1 AND t2.text_id = 16;
) p where p.a is not null
Comments