tuyenle tuyenle - 19 days ago 10
MySQL Question

IFNULL in nested select statement mysql

I want to return a message when there is no row found in a

nested SELECT
statement like so. In this case, when there is no
Lake
in a row, the return message is 'no lake found'. I tried
IFNULL
but it return nothing when there is no lake.

SELECT GROUP_CONCAT(concat_ws(', ', Lake_name,
IFNULL(Area, 'not available'),
IFNULL(Altitude, 'not available'),
IFNULL(Depth, 'not available'),
IFNULL(River_outflow, 'not available')
) SEPARATOR '; ')
From LAKE WHERE IFNULL(Lake_name, 'no lakes found') IN
(SELECT Lake FROM LOCATION where County IN
(SELECT County FROM CITY WHERE City_name='$userinput')) GROUP BY NULL;";

Answer

I think you should select this way

  SELECT CASE when Lake_name is null then 'no lakes found' else 
       GROUP_CONCAT(concat_ws(', ', Lake_name, 
                                      IFNULL(Area, 'not available'),                             
                                      IFNULL(Altitude, 'not available'), 
                                      IFNULL(Depth, 'not available'), 
                                      IFNULL(River_outflow, 'not available')
                               ) SEPARATOR '; ') END my_lake
  From LAKE 
  WHERE LAKE_name  IN  (SELECT  Lake_name FROM LOCATION where County IN 
                        (SELECT County FROM CITY WHERE City_name='$userinput')) 
  OR LAKE_name is null ;