MySQL return values when no rows found

Question

I want to return a MySQL result set even when no rows are found that simulates the correct result set with default values.

This is so I don't have to have any logic in my display of this data... I can just push it out.

What I would like to do is always return 3 Warehouses... 000 CAL RET with values of 0 when nothing is found.

If a new warehouse is ever added I would hopefully be able to modify this to include that warehouse also.

Here goes:

http://sqlfiddle.com/#!9/e94bc

I would want to return another row for RET warehouse with zeros in all columns.

If you query another ItemCode that doesn't exist, I want to see all 3 rows 000 CAL RET with zero values in each column.

as a bonus I would also like to always return 0 and not (NULL) as a value.

I have searched for answers, but can't seem to find exactly what I need.

Thanks!

https://stackoverflow.com/questions/51168676/mysql-return-default-value-if-no-rows-returned Hope this post helps! — KRISHNA KANT SINGH, May 19 '19 at 15:19

Barmar · Answer 1 · 2015-09-24T18:42:55.050

1

SELECT querytype, col1, col2, col3, ...
FROM (SELECT 1 AS querytype, col1, col2, col3, ...
      FROM YourTable
      WHERE ...
      UNION ALL
      SELECT 2 AS querytype, 0, 0, 0, ...
      UNION ALL
      SELECT 2 AS querytype, 0, 0, 0, ...
      UNION ALL
      SELECT 2 AS querytype, 0, 0, 0, ...) AS x
HAVING querytype = MIN(querytype)

Everything before the first UNION ALL is your regular query, but with the added querytype column. If that query returns anything, MIN(querytype) will be 1, and those rows will match the HAVING clause. Otherwise, MIN(querytype) will be 2, and the default rows will match.

edited Sep 24 '15 at 18:42

answered Sep 24 '15 at 17:25

Barmar

741,623
53
500
612

You have a tiny error in your query, It should be `SELECT querytype, col1, col2, col3, ...`. Otherwise, querytype won't be found. – Jacobian Sep 24 '15 at 17:32

pancho018 · Accepted Answer · 2015-09-24T19:01:42.080

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'

Actually this seems so close to working, but when I query it with an item code that doesn't exist I don't get any values. Also it does not fill in the empty rows from what I can tell. — Yourguide, Sep 24 '15 at 18:58
Ah, the where should be part of the join instead so it only joins on those records that match the string. — pancho018, Sep 24 '15 at 19:00

score 0 · Answer 3 · answered Sep 04 '17 at 08:51

I've just faced the same situation, and I could manage to return values in a similar way as Barmar describes, slightly more simple though.

SELECT mycol FROM (
  SELECT mycol FROM mytable WHERE condcol = 'some value' 
  UNION SELECT 'hardcoded value'
) alias
LIMIT 1;

If the first query doesn't return any row, the second will always return the hard coded value. If you need to return more hard coded rows, just add UNION SELECT 'another value' as many times as you need. The LIMIT statement at the end lets you make sure that you wouldn't return the hard-coded rows if you find real rows.

Note that your original query would become a derived query and it must have an alias. Otherwise, you will see this error: Error in query (1248): Every derived table must have its own alias. I hope this helps!

MySQL return values when no rows found

3 Answers3