CODEWITHSUNDEEP
mysqlsql
luqita
luqita

Reputation: 4087

Avoiding a null return set

How can I make a query always return something, a default value when it does not find what I am looking for?

For example, in the following query:

SELECT 
            food_id,
            drink_id,
            payment_amount, 
            count(*) AS total_payments,
            payment_amount * count(*) AS total_benefit
            FROM foods pc
            INNER JOIN drinks px
            ON pc.id = px.food_id
            AND pc.drink_id = 25
            GROUP BY food_id, drink_id;

when pc.id is not equal to px.food_id I get an empty set. I am looking for something such as:

SELECT 
            food_id DEFAULT IF NOTHING 5,
            drink_id DEFAULT IF NOTHING 25,
            payment_amount DEFAULT IF NOTHING 0, 
            count(*) AS total_payments DEFAULT IF NOTHING 0,
            payment_amount * count(*) AS total_benefit DEFAULT 0
            FROM foods pc
            INNER JOIN drinks px
            ON pc.id = px.food_id
            AND pc.drink_id = 25
            GROUP BY food_id, drink_id;

Any ways of doing something like the above?

Upvotes: 1

Views: 147

Answers (3)

onedaywhen
onedaywhen

Reputation: 57063

SELECT food_id, drink_id, payment_amount, COUNT(*) AS total_payments,
      payment_amount * COUNT(*) AS total_benefit
   FROM foods pc 
      INNER JOIN drinks px
         ON pc.id = px.food_id
            AND pc.drink_id = 25
   GROUP BY food_id, drink_id 
UNION
SELECT 0, 0, 0, 0, 0
   FROM MySystemTableAlwaysHasExactlyOneRow 
  WHERE NOT EXISTS (
                    SELECT * 
                      FROM foods pc 
                         INNER JOIN drinks px
                            ON pc.id = px.food_id
                               AND pc.drink_id = 25
                     ) ;

Upvotes: 0

Tevo D
Tevo D

Reputation: 3381

You are getting an empty result set because of the inner join. This should probably be an outer join. Combining this with Rup's answer is probably what you are looking for:

SELECT 
        COALESCE(food_id, 0) AS food_id,
        COALESCE(drink_id, 25) AS drink_id,
        COALESCE(payment_amount, 0) as payment_amount,
        count(*) AS total_payment,
        COALESCE(payment_amount, 0) * count(*) AS total_benefit
        FROM foods pc
        LEFT OUTER JOIN drinks px
        ON pc.id = px.food_id
        AND pc.drink_id = 25
        GROUP BY food_id, drink_id;

Upvotes: 3

Rup
Rup

Reputation: 34418

You probably want COALESCE:

SELECT 
            COALESCE(food_id, 0) AS food_id,
            COALESCE(drink_id, 25) AS drink_id,
            COALESCE(payment_amount, 0) as payment_amount,
            count(*) AS total_payment,
            COALESCE(payment_amount, 0) * count(*) AS total_benefit
            ...
GROUP BY COALESCE(food_id, 0), COALESCE(drink_id, 25),
         COALESCE(payment_amount, 0);

etc.

Upvotes: 1

Related Questions