CODEWITHSUNDEEP
mysqldatabase
Jimbo.Koen
Jimbo.Koen

Reputation: 151

MySQL: Tricky Query with regard to CASES

I have to solve a problem with a very complicated Query. Let´s sum it up for you:

The function should receive a customer number as an input parameter. If this 
number is not contained in the tablecustomer, - 1 is to be output.
Otherwise, the customer bonus points are to be calculated. These result from 
the reservations made by the customer.
The bonus points per reservation are calculated by multiplying the number of 
reserved seats by the flight duration of the flight.

I already tried my best and worked this out:

CREATE FUNCTION customer_bonus(inputnumber INT(11))
RETURNS int
DETERMINISTIC
RETURN 
    CASE
        WHEN 
           (SELECT COUNT(customer.ID) FROM customer WHERE inputnumber = 
         customer.ID) >= 1
        THEN 
            (SELECT  customer.ID, SUM(SELECT 
            flightexecution.FlightDurationInMinutes * 
            reservation.NoReservedSeats from customer, flightexecution, 
             reservation where inputnumber = customer.ID))
    from customer, flightexecution, reservation 

    WHERE reservation.CustomerID = customer.ID AND customer.ID = inputnumber 
    AND flightexecution.FlightNo = reservation.FlightNo
    AND reservation.DepartureDate = flightexecution.DepartureDate)

    ELSE -1
END;

I run this query to test it: select c.id, customer_bonus(c.ID) from customer c;

The result is just an 'OK' which means that something is wrong.

The funny thing: When i try out the simple Select Statements it works..

Could somebody of you help me?

Upvotes: 0

Views: 19

Answers (1)

Uueerdo
Uueerdo

Reputation: 15951

The query you are using is likely returning a number of 2-field results. When attempting to return an int sourced from a query, the query can have only one result with a single field convertible to int. I think this is the query you are probably looking for:

SELECT SUM(fe.FlightDurationInMinutes * r.NoReservedSeats) AS bonusPoints
FROM reservation AS r
INNER JOIN flightexecution AS fe 
   ON r.FlightNo = fe.FlightNo
   AND r.DepartureDate = fe.DepartureDate
WHERE r.CustomerID = inputnumber

You'll notice the query becomes much clearer when explicit JOINs are used. Honestly, I have no idea why anyone would teach implicit joins as anything more than a curiosity anymore. They've been seen as poor form (at best) for nearly two decades now.

With explicit joins, it would have been obvious that your SUM(SELECT subquery had no join conditions at all; meaning the sum you ended up calculating would have been of the [flight duration] * [reserved seats] of every combination of flightexecution and reservation, regardless of their relation to the customer or each other.

Upvotes: 1

Related Questions