title: Assigning the right value to the customerIDs in a sql function
body: <p>I have written a sql function for a flight customer database which calculates the bonus for customers. Suppose there is a table customer and a table reservations. If a customer reserves some seats on a flight, it will get a bonus with respect to the flight duration. If the customerID doesn't appear in the reservation table it will return the bonus otherwise it calculated the bonus.</p>
  
  <pre><code>CREATE FUNCTION `test5`(ID int(11) ) RETURNS int(11)
  BEGIN DECLARE result int;
      if (select ID from customer where ID not in (select CustomerID from reservation) limit 1) then set result = -1;
      else if (select ID from customer where ID in (select CustomerID from reservation) limit 1) then set result = (select sum(c.NoReservedSeats*f.FlightDurationInMinutes) as Bonus 
      from reservation c, flightexecution f where c.FlightNo=f.FlightNo 
      and f.DepartureDateAndTimeUTC = c.DepartureDateAndTimeUTC 
      and c.CustomerID = c.CustomerID group by c.CustomerID limit 1) ;
      end if;
      end if;
  RETURN result;
  END
  
  </code></pre>
  
  <p>So these are the bonus values for the CustomerIDs in the reservation table</p>
  
  <pre><code>+-------+
  | Bonus |
  +-------+
  |   360 |
  |   180 |
  |  2280 |
  |  2040 |
  |   180 |
  |  7180 |
  +-------+
  </code></pre>
  
  <p>So the first if statement works, but second one doesn't work, I have to set limit 1 because otherwise get the "Error 1242 Subquery returns more than one row". But with this limit 1 expression I get for every customer 360 bonus which is my problem. </p>