MySql, return default value if no rows returned

Question

I have a query as below

SELECT cap_id FROM cap_master WHERE   
        (cap_type = 'Type1' AND cap_desc = 'CapDesc1') 
     OR (cap_type = 'Type2' AND cap_desc = 'CapDesc2')
     OR (cap_type = 'Type3' AND cap_desc = 'CapDesc3')
     OR (cap_type = 'Type4' AND cap_desc = 'CapDesc4')
order by cap_type,cap_desc

This returns multiple rows based on where condition, what i am looking for is like for a condition which do not return any rows, i should have a default value say '0'. As of now i do not get any row for it.

For e.g if 3rd condition (cap_type = 'Type3' AND cap_desc = 'CapDesc3') do not match, i am expecting an output as below:

I checked solutions given, like

Return a value if no rows match

Return Default value if no row found -mysql

But seems they don't work on multiple rows getting returned. Any pointers will be greatly appreciated.

Here's a Fiddle to play with.

It would help if you posted sample data and expected results, ideally as a SQL Fiddle. — Neville Kuyt, Jul 04 '18 at 08:33
If you have a reference table with all the possible types, then you could perform a `OUTER JOIN` by `cap_type`. So you would know _which_ type is missing — schlonzo, Jul 04 '18 at 08:47
Is there another table containing all possible types? @pranav — schlonzo, Jul 04 '18 at 09:48
Where do these different types come from, that you use in the `WHERE` clause? — schlonzo, Jul 04 '18 at 10:11
Basically i get them in Json response, i use them to query the DB to get the IDs. From that list, I have know what matched what not (If i get a query which works for the situation, i can know all in one shot). — pranav, Jul 04 '18 at 10:20

score 1 · Answer 1 · answered Jul 04 '18 at 08:37

1

I'd use IFNULL to do what you want. It is untested since you didn't provide a minimal set of data to run the query on:

SELECT IFNULL( (SELECT cap_id FROM cap_master WHERE   
        (cap_type = 'Type1' AND cap_desc = 'CapDesc1`) 
     OR (cap_type = 'Type2' AND cap_desc = 'CapDesc2')
     OR (cap_type = 'Type3' AND cap_desc = 'CapDesc3')
     OR (cap_type = 'Type4' AND cap_desc = 'CapDesc4')
order by cap_type,cap_desc) ,'0');

Here you can find some more detail on IFNULL

answered Jul 04 '18 at 08:37

Lelio Faieta

6,457
7
40
74

Wouldn't this return `0` only, if there is _no_ data with _Type1_, _Type2_, _Type3_ AND _Type4_? He wants the `0` only if a specific Type is missing (for that result row) – schlonzo Jul 04 '18 at 08:53
no, it should do its job (but I didn't test it with OP's data). The only other option if this doesn't work is to split the query into 4 subqueries – Lelio Faieta Jul 04 '18 at 08:55
it would but it is the most unefficient way to do it – Lelio Faieta Jul 04 '18 at 08:59
Tried using IFNULL as this, it seems it wont work on multiple rows. Gives 'Subquery returns more than 1 row'. – pranav Jul 04 '18 at 09:05
So I tried your query. Either it returns an error, because there is more than one row coming back or it's like I said - it only works, when there is no data fullfilling any of the conditions: http://sqlfiddle.com/#!9/d2a4a4/1/0 – schlonzo Jul 04 '18 at 09:06

Gordon Linoff · Accepted Answer · 2018-07-11T12:05:38.563

1

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.

edited Jul 11 '18 at 12:05

answered Jul 04 '18 at 12:14

Gordon Linoff

1,242,037
58
646
786

I tried similar to this, but this wont maintain the order. Can check with Fiddle provided. http://sqlfiddle.com/#!9/4b9bb0/2 – pranav Jul 04 '18 at 12:22
I added a solution which seems to work fine. If it would hurt efficiency, a better and efficient solution is always welcome :) – pranav Jul 04 '18 at 12:45
@pranav . . . This query appears to do exactly what you ask for: http://sqlfiddle.com/#!9/8661c7/1. – Gordon Linoff Jul 04 '18 at 17:08
Thanks Gordon, now it works. So we have 2 ways to do now. I tried with IFNULL, which worked fine too.This is concise, will use this. – pranav Jul 05 '18 at 07:33
It gives: 'Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' ' in my database. People who face this can fix as given here: https://stackoverflow.com/questions/1008287/illegal-mix-of-collations-mysql-error – pranav Jul 05 '18 at 07:49
seems the join won't help if any column is null, or am i doing it wrong: http://sqlfiddle.com/#!9/fa74be/1 – pranav Jul 11 '18 at 08:15
@pranav . . . I added the logic to fix that problem. – Gordon Linoff Jul 11 '18 at 12:25
Thanks again Gordon !! – pranav Jul 11 '18 at 15:20

score 1 · Answer 3 · answered Jul 04 '18 at 14:06

A solution which i was able to figure out. Not sure if this hurts efficiency much, i guess at a time i will have 20 conditions max. Let me know if there's some alternative.

SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type4' AND 
cap_desc = 'CapDesc4' ),0) as cap_id
union all
SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type7' AND 
cap_desc = 'CapDesc7' ),0) as cap_id
union all
SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type3' AND 
cap_desc = 'CapDesc3' ),0) as cap_id
union all
SELECT ifnull((SELECT cap_id FROM cap_master WHERE cap_type = 'Type6' AND 
cap_desc = 'CapDesc6' ),0) as cap_id

score 0 · Answer 4 · answered Jul 04 '18 at 08:30

0

try CASE function :) You can assign what and where should be shown

https://www.w3schools.com/sql/func_mysql_case.asp

answered Jul 04 '18 at 08:30

Antonina K

425
4
12

`CASE` would not help here – schlonzo Jul 04 '18 at 08:35
i think CASE would be helpful where we get a row. Here it's like there is no row returned to write a case on it. – pranav Jul 04 '18 at 09:08

MySql, return default value if no rows returned

4 Answers4

Linked