Issue

This Content is from Stack Overflow. Question asked by codyLine

I have a select statement involving 3 different tables through left joins: booking, area and airport. In the statement I’m trying to get the sum over the booking prices and the number of these bookings. These bookings must meet the requirements in the case clauses.

Here is the my query:

SELECT sum(CASE WHEN (dateType1 AND airport1) THEN com ELSE 0 END),
count(dateType1 AND airport1),
sum(CASE WHEN (dateType2 AND airport2) THEN com ELSE 0 END),
count(dateType2 AND airport2)
FROM (SELECT ((a.price * a.effektor)/100) AS com,
CASE WHEN ((a.booking_date >= '2022-03-01T00:00' AND a.booking_date <= '2022-03-31T23:59:59')) THEN TRUE END dateType1,
CASE WHEN (ar.airport_id = 10) THEN TRUE END airport1,
CASE WHEN ((a.booking_date >= '2022-03-01T00:00' AND a.booking_date <= '2022-03-31T23:59:59')) THEN TRUE END dateType2,
CASE WHEN (ar.airport_id = 2) THEN TRUE END airport2
FROM booking a
LEFT JOIN area p ON a.areaid = p.areaid
LEFT JOIN airport ar ON ar.airport_id = p.airport_id
WHERE a.status != 'STO' AND p.areaid = 10459) booking

Unfortunately the query throws errors.
When I remove the case clauses with ar.airport_id, the query run without issues and I get some results.
Why is the first statement with all the requirements I need not working?

Any help is greatly appreciated.

Solution

The problem might be with your left joins. Since you have them in the where clause you are changing your left join into an inner join. This is because x = null can never be true so you can never have null values for those joined columns.

I would fix it by changing your example like this:

FROM booking a
LEFT JOIN area p ON a.areaid = p.areaid and a.status != 'STO' 
LEFT JOIN airport ar ON p.airport_id = ar.airport_id and ar.airport_id = 2
WHERE AND p.areaid = 10459

I don’t see a difference between dataType1 and dateType2 — which makes me think that might be a bug. Other than that I re-wrote the query like this — should be find. If you have some sample data you can show that might help.

SELECT 
  sum(CASE WHEN dateType1 AND airport1 THEN com ELSE 0 END) AS type1sum,
  sum(CASE WHEN dateType1 AND airport1 THEN 1 ELSE 0 END) AS type1count,
  sum(CASE WHEN dateType2 AND airport2 THEN com ELSE 0 END) AS type2sum,
  sum(CASE WHEN dateType2 AND airport2 THEN 1 ELSE 0 END) AS type2count
  FROM (
    SELECT ((a.price * a.effektor)/100) AS com,
      CASE WHEN DATE(a.booking_date) = '2022-03-01' THEN TRUE ELSE FALSE END dateType1,
      ar.airport_id = 10 AS airport1,
      CASE WHEN DATE(a.booking_date) = '2022-03-01' THEN TRUE ELSE FALSE END dateType2,
      ar.airport_id = 2 AS airport2
    FROM booking a
    LEFT JOIN area p ON a.areaid = p.areaid
    LEFT JOIN airport ar ON ar.airport_id = p.airport_id
    WHERE a.status != 'STO' AND p.areaid = 10459
  ) booking

This Question was asked in StackOverflow by codyLine and Answered by Hogan It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.