HAVING vs WHERE vs GROUP BY clauses, when to use them and if you use ' '

Question

Hopefully this post will help me and many others like me better understand the issues of WHERE, HAVING, GROUP BY etc. Everyone has their own way of doing syntax and since there is more than one way to make something work in MYSQL the idea would be to help me make this work while helping the community at large too :) Below is one suggested way of designing my query.

SELECT t1.post_id, t2.name,
           MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  Email,
       MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL END) as CustomerId,
       MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryDate,
        MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryTime,
        MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryType,
          MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  Zip,
       MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  OrderNote,
       MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  PaymentTotal,
       MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  OrderStatus

FROM table_A  t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
where  OrderStatus rlike '%trans%|ready'
    and DeliveryDate >= current_date - interval 7 day
    and DeliveryType = 'pickup'
group by 
    t1.post_id, 
    t2.name

This produces an error >>>> "#1054 - Unknown column 'DeliveryDate' in 'where clause'" I presume it produces this error since "orderStatus" is not an actual column name but is a value being pulled from another column and then being made its own column through the :

MAX(case when meta_key = '_order_status' THEN `meta_value` ELSE NULL END) as  OrderStatus

So I presumed that I needed to enclose the name in ' ' both in the SELECT area of the statements and in the WHERE area. BUT that produces the error >>>>>>>>>>>> "Warning: #1292 Truncated incorrect date value: 'DeliveryDate'"

Why would this be and whats the solution?

EDITING Because some have suggested the WHERE clause can not be used in the manner above, I have used the HAVING clause using the code below. Here is the code:

SELECT.......^^from above..............

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) 
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

The above doesnt work either. The issue here is that the AND clauses are more important and seem to kncok out the date filter. When I use this code, this returns all records regardless of dates.

EDIT 2 >>>>>>>>>> Tried this too btu it still doesnt filter out the 3 month old entry

SELECT.......^^from above..............

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)>= current_date - interval 7 day 
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

EDIT 3 >>>>>>>>>> Simplifying the code. Same result. Even with CURDATE() still shows 3 month old records

......................

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)= CURDATE()
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

EDIT 4 >>>>>>>>>>>>>>>>>>>>>> minimal exmaple...

SELECT t1.post_id, t2.name,

   MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryDate,
    MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryTime,
    MAX(CASE WHEN `meta_key` = 'value' THEN `meta_value` ELSE NULL  END) as DeliveryType,
   MAX(case when meta_key = 'value' THEN `meta_value` ELSE NULL END) as  OrderStatus

FROM table_A t1
inner join table_B t2 on find_in_set(t1.post_id, t2.payment_ids)
GROUP BY post_id
HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END)= CURDATE()
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'
ORDER BY 'DeliveryTime'  DESC

I expect this to return the records only of today. IT is return all records of all time while meeting the other HAVING clause requirements

Answer 1

The answer as per @O. Jones is a nested query:

SELECT post_id
     , name
     , Email
     , CustomerId
     , DeliveryDate
     , DeliveryTime
     , DeliveryType
     , Zip
     , OrderNote
     , PaymentTotal
     , OrderStatus
  FROM ( SELECT t1.post_id
              , t2.name
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Email
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as CustomerId
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryDate
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryTime
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as DeliveryType
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as Zip
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as OrderNote
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as PaymentTotal
              , MAX(CASE WHEN meta_key = 'value' THEN meta_value ELSE NULL END) as OrderStatus
           FROM table_A t1
         INNER 
           JOIN table_B t2 
             ON FIND_IN_SET(t1.post_id, t2.payment_ids)  
         GROUP 
             BY t1.post_id
              , t2.name  
       ) AS derived_table
 WHERE OrderStatus RLIKE '%trans%|ready'
   AND DeliveryDate >= CURRENT_DATE - INTERVAL 7 DAY
   AND DeliveryType = 'pickup'