CODEWITHSUNDEEP
mysql
eshaa
eshaa

Reputation: 406

If no records found, use another query

SELECT 
    GROUP_CONCAT(sub.value) AS DURATION
FROM
    (SELECT 
        TENANT_SETTING_VALUE AS VALUE
    FROM
        MST_SITE_PREFERENCES
    WHERE
        `TENANT_ID` = '{0559cdcb-c63b-4c81-be91-b78Tenant1000' 
    UNION 
    SELECT 
        DEFAULT_SETTING
    FROM
        `GLO_SITE_PREFERENCES` AS VALUE
    WHERE
        `SETTING_ID` = 'ESTIMATED_SAVINGS_DURATION_IN_MONTHS') sub;

The above query returns value like

130,50

Requirement here is if no matches found for second query, it should return the first value - 130. If found, it should return the value 50. So all the time the query should return only one value.

Upvotes: 0

Views: 63

Answers (2)

G one
G one

Reputation: 2729

SELECT GROUP_CONCAT(sub.value) AS DURATION
FROM
( case 
  when
  ((SELECT count(TENANT_SETTING_VALUE) AS VALUE 
      FROM MST_SITE_PREFERENCES 
    WHERE `TENANT_ID` = '{0559cdcb-c63b-4c81-be91-b78Tenant1000')>0)
  then
    SELECT TENANT_SETTING_VALUE AS VALUE 
      FROM MST_SITE_PREFERENCES 
    WHERE `TENANT_ID` = '{0559cdcb-c63b-4c81-be91-b78Tenant1000')
  when
  ((SELECT count(DEFAULT_SETTING) AS VALUE
    FROM `GLO_SITE_PREFERENCES` 
     WHERE `SETTING_ID`='ESTIMATED_SAVINGS_DURATION_IN_MONTHS')>0)
   then
    SELECT DEFAULT_SETTING AS VALUE
    FROM `GLO_SITE_PREFERENCES` 
     WHERE `SETTING_ID`='ESTIMATED_SAVINGS_DURATION_IN_MONTHS'
) sub;

This works provided your subquery returns just 1 row

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I think this version does what you want (although I am unclear on which has precedence, the first or second query). 

select value
from ((select TENANT_SETTING_VALUE as value, 1 as which
       from MST_SITE_PREFERENCES
       where `TENANT_ID` = '{0559cdcb-c63b-4c81-be91-b78Tenant1000'
      ) union all
      (select DEFAULT_SETTING, 2 as which
       from `GLO_SITE_PREFERENCES` AS VALUE
       where `SETTING_ID` = 'ESTIMATED_SAVINGS_DURATION_IN_MONTHS'
      ) 
     ) t
order by which
limit 1;

If the precedence is wrong, just switch the values on which.

You could also express this as:

select TENANT_SETTING_VALUE as value, 1 as which
from MST_SITE_PREFERENCES
where `TENANT_ID` = '{0559cdcb-c63b-4c81-be91-b78Tenant1000'
union all
select DEFAULT_SETTING, 2 as which
from `GLO_SITE_PREFERENCES` AS VALUE
where `SETTING_ID` = 'ESTIMATED_SAVINGS_DURATION_IN_MONTHS' and
       not exists (select TENANT_SETTING_VALUE as value, 1 as which
                   from MST_SITE_PREFERENCES
                   where `TENANT_ID` = '{0559cdcb-c63b-4c81-be91-b78Tenant1000'
                  );

Similarly here, if the precedence is wrong, the subqueries need to be rearranged.

Upvotes: 2

Related Questions