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
– Neville Kuyt, Commented Jul 4, 2018 at 8: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
– schlonzo, Commented Jul 4, 2018 at 8:47
Is there another table containing all possible types? @pranav — schlonzo
– schlonzo, Commented Jul 4, 2018 at 9:48

Gordon Linoff · Accepted Answer · 2018-07-11 12:05:38Z

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, 2018 at 12:05

answered Jul 4, 2018 at 12:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

pranav Over a year ago

I tried similar to this, but this wont maintain the order. Can check with Fiddle provided. sqlfiddle.com/#!9/4b9bb0/2

pranav Over a year ago

I added a solution which seems to work fine. If it would hurt efficiency, a better and efficient solution is always welcome :)

Gordon Linoff Over a year ago

@pranav . . . This query appears to do exactly what you ask for: sqlfiddle.com/#!9/8661c7/1.

pranav Over a year ago

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 Over a year ago

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: stackoverflow.com/questions/1008287/…

|

user2399035 · Accepted Answer · 2018-07-04 08:37:23Z

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 4, 2018 at 8:37

user2399035

5 Comments

schlonzo Over a year ago

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)

user2399035 Over a year ago

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

user2399035 Over a year ago

it would but it is the most unefficient way to do it

pranav Over a year ago

Tried using IFNULL as this, it seems it wont work on multiple rows. Gives 'Subquery returns more than 1 row'.

schlonzo Over a year ago

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: sqlfiddle.com/#!9/d2a4a4/1/0

pranav · Accepted Answer · 2018-07-04 14:06:15Z

1

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

answered Jul 4, 2018 at 14:06

pranav

4311 gold badge12 silver badges28 bronze badges

Comments

Antonina K · Accepted Answer · 2018-07-04 08:30:34Z

0

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

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

answered Jul 4, 2018 at 8:30

Antonina K

4694 silver badges12 bronze badges

2 Comments

schlonzo Over a year ago

CASE would not help here

pranav Over a year ago

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.

Collectives™ on Stack Overflow

MySql, return default value if no rows returned

4 Answers 4

8 Comments

5 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related