SQL query with Case When statements and joining multiple tables not working

codyLine 0
Viewed 60

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,

SELECT 
   sum(CASE WHEN dateType1 THEN com ELSE 0 END),
   count(dateType1),
   sum(CASE WHEN dateType2 THEN com ELSE 0 END),
   count(dateType2)
   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 ((a.booking_date >= '2022-03-01T00:00' AND a.booking_date <= '2022-03-31T23:59:59')) THEN TRUE END dateType2    
        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

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.

EDIT
Maybe to two dateTypes in the query are confusing. To make it more simple, even this query is giving zero and null back:

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

When I remove AND ar.airport_id = 2 from the query and run the query again, I get reasonable data.
Something is wrong with the second join. I don't know what it is!

select sql
1 Answers

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
Hogan0
Related