title: mysql select with if condition
body: <p>I have a function in Mysql that is called like this:</p>
  
  <pre><code>  DB::raw("count_adults(rooms.id) as adults"),
  </code></pre>
  
  <p>count_adults is this:</p>
  
  <pre><code>BEGIN
  
   DECLARE adults INT;
  
      SELECT  
       count(*) INTO adults 
      FROM 
       clients 
      WHERE 
       clients.room_id = r_id 
          and (age &gt;= 18 
          or age = 0);
              RETURN adults;
      END
  </code></pre>
  
  <p>However I want to if this query returns no results(0) do another select or function, like this:</p>
  
  <pre><code>   SELECT count(*) INTO adults  FROM client_room, clients    
  WHERE client_id = clients.id and client_room.room_id = r_id and (age &gt;18 or age = 0)
  </code></pre>
  
  <p>Can I create a if condition? IN the php code? Or in the query? Something like this:</p>
  
  <pre><code>IF EXISTS (SELECT 1 FROM clients WHERE clients.room_id = r_id) 
  
  
  BEGIN
  
  DECLARE adults INT;
  
      SELECT  
          count(*) INTO adults 
      FROM 
          clients 
      WHERE 
          clients.room_id = r_id 
          and (age &gt;= 18 
          or age = 0);
  
     RETURN adults;
  END
  ELSE 
  
  BEGIN
  DECLARE adults INT;
  SELECT count(*) INTO adults 
  FROM 
  client_room, clients
  WHERE 
  client_id = clients.id and client_room.room_id = r_id 
  and (age &gt;18 
  or age = 0);
  
  END
  </code></pre>
  
  <p>I have tried this and it seems to work:</p>
  
  <pre><code>BEGIN
  
   DECLARE adults INT;
  
      SELECT COUNT(*) INTO adults
      FROM clients
      WHERE clients.room_id = r_id
       and (age &gt;= 18 or age = 0);
  
      RETURN IF( adults&gt;0, adults, (SELECT count(*) FROM client_room, clients
           WHERE client_id = clients.id
           and client_room.room_id = r_id)
               );
  
     END
  </code></pre>