return row even if all no value found in table mysql

Question

I Have a select where I am trying to return a row even if there is nothing to be found from the select.

Here is the select

                    select  
                    1 as risk_management,
                    0 as Borrow,  
                    0 as Interest, 
                    IFNULL(d.symbol,'E') as symbol, 
                    IFNULL(d.Abbreviation,'EUR') as Abbreviation, 
                    IFNULL(sum(round((a.amount_financed - a.amount_invested - a.amount_withdrawn) * i.average_rate / j.average_rate, 2)),0)  as LendingOffers,
                    IFNULL( min(a.Interest),0) as InterestLend, 
                    0 as VolumePerDay,
                    0 as LatestId, 
                    0 as InterestLatestRealized,
                    0 as InterestBorrowLow,
                    IFNULL(max(a.Interest),0) as InterestLendHigh
            from    market_cap a
            where   ........more statements here...

But when I run this select I still get nothing returned. I would like mysql to generate a row that has 0 for numbers and 'E' and 'EUR' if the value is missing, I thought IFNULL works for that after searching other stackoverflow but its not working in my case.

No clue what you are trying to say here. Do you want to get a record returned even when the WHERE clause doesn’t match anything? That is of course not possible this way. — CBroe, Mar 16 '18 at 09:07
@CBroe Yea thats exactly what I mean, I am trying to return basically 0 and stuff if no matches are in where clause. — Masnad Nihit, Mar 16 '18 at 09:08
https://stackoverflow.com/questions/23558295/return-default-value-if-no-row-found-mysql explains an approach, whether that will work for you in a similar fashion you’ll have to go figure out, because we don’t know enough about your data model and what exactly you want to select to begin with. If that doesn’t do it, then do some (more) research of your own first please. — CBroe, Mar 16 '18 at 09:11
Any reason why you don't just construct this dummy row in your application in the event that no rows are returned from the select? — Paul Campbell, Mar 16 '18 at 09:27

score 0 · Answer 1 · answered Apr 02 '18 at 14:26

Since I don't have your data I cannot test the query for you, but I can demonstrate you the basic idea.

You need to create a buffer table with your default data as the main subselect of your query. In my example, it is called "dv" as in "default values".

The query which fetches the real values is also a subquery in the from clause. In my example, it is called "rv" as in "real values".

I use a left (outer) join on to join both select statements with a condition which is always true (on 1 = 1).

Therefore, when the query which fetches the real values cannot find any results, we can still use the values in the default table.

select  
                IFNULL(rv.risk_management, dv.risk_management) as risk_management,
                IFNULL(rv.Borrow, dv.Borrow) as Borrow,
                IFNULL(rv.Interest, dv.Interest) as Interest,
                IFNULL(rv.symbol, dv.symbol) as symbol,
                IFNULL(rv.Abbreviation, dv.Abbreviation) as Abbreviation,
                IFNULL(rv.LendingOffers, dv.LendingOffers) as LendingOffers,
                IFNULL(rv.InterestLend, dv.InterestLend) as InterestLend,
                IFNULL(rv.VolumePerDay, dv.VolumePerDay) as VolumePerDay,
                IFNULL(rv.LatestId, dv.LatestId) as LatestId,
                IFNULL(rv.InterestLatestRealized, dv.InterestLatestRealized) as InterestLatestRealized,
                IFNULL(rv.InterestBorrowLow, dv.InterestBorrowLow) as InterestBorrowLow,
                IFNULL(rv.InterestLendHigh, dv.InterestLendHigh) as InterestLendHigh
            from (
                1 as risk_management,
                0 as Borrow,  
                0 as Interest, 
                'E' as symbol, 
                'EUR' as Abbreviation
                0 as LendingOffers,
                0 as InterestLend,
                0 as VolumePerDay,
                0 as LatestId, 
                0 as InterestLatestRealized,
                0 as InterestBorrowLow,
                0 as InterestLendHigh
            ) as dv
            LEFT JOIN (
                select  
                    risk_management,
                    Borrow,  
                    Interest, 
                    d.symbol, 
                    d.Abbreviation, 
                    sum(round((a.amount_financed - a.amount_invested - a.amount_withdrawn) * i.average_rate / j.average_rate, 2)) as LendingOffers,
                    min(a.Interest) as InterestLend, 
                    VolumePerDay,
                    LatestId, 
                    InterestLatestRealized,
                    InterestBorrowLow,
                    max(a.Interest) as InterestLendHigh
                from market_cap a
                where   ........more statements here...
            ) AS rv
            ON 1 = 1

Good succes and have a nice day Masnad Nihit

return row even if all no value found in table mysql

1 Answers1