CODEWITHSUNDEEP
mysqlsql
pranav
pranav

Reputation: 431

MySql, return default value if no rows returned

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:

23
34
0  
45

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.

Upvotes: 1

Views: 3290

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

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.

Upvotes: 1

pranav
pranav

Reputation: 431

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

Upvotes: 1

Lelio Faieta
Lelio Faieta

Reputation: 6684

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

Upvotes: 1

Antonina K
Antonina K

Reputation: 469

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

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

Upvotes: 0

Related Questions