Mysql Return Result If 0 Rows Returned

Returning a value even if no result

MySQL has a function to return a value if the result is null. You can use it on a whole query:

SELECT IFNULL( (SELECT field1 FROM table WHERE id = 123 LIMIT 1) ,'not found');

Select mysql returns 0 rows

When you list all your tables in a FROM clause, it produces the Cartesian product of those tables. This link is additional info on the Cartesian product of two tables

This is fine if you are selecting specific data you know is there. However, if one of those tables is empty, the Cartesian product will return an empty set.

Here is a SQL fiddle of this: Three tables, one is empty

As you can see, there are three tables, I have selected a value from one table, but because my FROM statement has all three tables, including an empty one, there is no data to select from, and so it returns 0 rows.

However in this example: Two tables with data only

I have selected the same column, but my FROM statement has only the tables with data, which returns rows as expected.

There are numerous ways to fix your query, one such method may be using a left join as described in this answer: Select multiple tables when one table is empty

Simple MySql select statement returns 0 rows

Please try with like operator.

select * from profiles WHERE country LIKE '%Sweden%'

MySql, return default value if no rows returned

You want a left join:

select coalesce(cm.cap_id, 0) as cap_id
from (select 'Type1' as cap_type, 'CapDesc1' as cap_desc union all
      select 'Type2' as cap_type, 'CapDesc2' as cap_desc union all
      select 'Type3' as cap_type, 'CapDesc3' as cap_desc union all
      select 'Type4' as cap_type, 'CapDesc4' as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and cm.cap_desc = c.cap_desc
order by c.cap_type, c.cap_desc;

If you need to support NULL cap_desc (which is not part of the original question), you can do:

select coalesce(cm.cap_id, 0) as cap_id
from (
      select 'Type5' as cap_type, null as cap_desc
     ) c left join
     cap_master cm
     on cm.cap_type = c.cap_type and 
        (cm.cap_desc = c.cap_desc or cm.cap_desc is null and c.cap_desc is null)
order by c.cap_type, c.cap_desc;

Here is a SQL Fiddle.

MYSQL if a select query returns 0 rows then another select query?

This appears to work from a quick test I just did and avoids the need to check for the existence of x=1 twice.

SELECT SQL_CALC_FOUND_ROWS *
FROM mytable
WHERE x = 1

UNION ALL

SELECT *
FROM mytable
WHERE 
FOUND_ROWS() = 0 AND x = 2;

Edit: Following your clarification to the question obviously the 2 queries will need to be UNION compatible for the above to work.

The answer to your updated question is No. This is not possible in a single query. You would need to use some conditional procedural logic to execute the desired query.

How to force a MySQL query to return a 0 when the result of query is nothing?

In your case, just remove the GROUP BY and use COALESCE():

SELECT COALESCE(SUM(quantity), 0) AS qt
FROM stockMove
WHERE date_mov >= '2019-04-01' AND date_mov <= '2019-04-30' AND
      ProductCode = '000807';

An aggregation query with no GROUP BY always returns one row, even when no rows match. The returned value is NULL -- which is where the COALESCE() comes in.

With the GROUP BY, the query returns no rows at all. Can't convert nothing into a 0.

MySQL update returns 0 rows affected while select returns results

By comparing GUI generated sql queries when I change something in GUI and my own queries. I found out the reason is I misspelled update to udpate. Now I begin to wonder how could mysql not complain about syntax error and just returns with 0 rows affected?

Well, at least now I know what to do when something odd happens.

MySQL return values when no rows found

you could create a master table of your warehouses with 0 in every column.

CREATE TABLE ItemWarehouseMaster
    (`Warehouse` varchar(3), `QOH` int, `QPO` int, `QSO` int, `QBO` int,`ItemCode` varchar(8))
;

INSERT INTO ItemWarehouseMaster
    (`Warehouse`, `QOH`, `QPO`, `QSO`, `QBO`, `ItemCode`)

    ('CAL', 0, 0, 0, 0, 0),
    ('RET', 0, 0, 0, 0, 0),
    ('000', 0, 0, 0, 0, 0),

then your query can join on this table and perform a coalesce on the 2 sets of data, for each column you should have a 0 from the mastertable and the value from your database, if the value from your database is null it will take the 0 from the 'master' table.

SELECT
    w.Warehouse,
    coalesce (w.QOH,Mast.QOH),
    coalesce (w.QSO,Mast.QSO),
    coalesce (w.QPO,Mast.QPO),
    coalesce (w.QBO,Mast.QBO)
FROM
    ItemWarehouseMaster Mast
left join
     ItemWarehouse w on
w.warehouse = mast.warehouse

And w.itemcode = '10052IP'

---

Related Topics