How return a value with mysql query depending on condition-CodePudding

I have the next mysql table:

Table(cl_convenios_do_medico)
id auto_increment
med_id  int
conv_id int

I´d like a sql to return '1':

If I found at least one "med_id" having a "conv_id".
If the table is empty

otherwise return '0'

I have tried something as:

select IFNULL( (select '1' from cl_convenios_do_medico CC where 
    CC.med_id=<my_med_id> and CC.conv_id=<my_conv_id> ) 
    and  exists(select '1' from cl_convenios_do_medico CC where CC.med_id=<my_med_id> limit 1),'0') as result

If seems to work when the table is not empty, but fail if table is empty.

Maybe someone has a simpler solution.

CodePudding user response：

Do something like this

SELECT MAX(result)
FROM (
 SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS result
 FROM cl_convenios_do_medico
 WHERE med_id = 8
 AND conv_id = 9
 UNION
 SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END
 FROM cl_convenios_do_medico
) c

DEMO

CodePudding user response：

You can use CASE statement

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

for example

SELECT CASE WHEN (SELECT COUNT(*) FROM employee) > 1 THEN '1' ELSE '0' END