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'

Answer 2

You are correct that WHERE clauses cannot refer to column aliases in the same query.

Think of it this way:

The first step of satisfying the query is constructing a virtual table from the FROM, JOIN, and ON clauses.

The second step is filtering that virtual table according to the WHERE clause.

The third step is reducing the virtual table if need be, according to GROUP BY and aggregate functions (SUM, COUNT, GROUP_CONCAT, etc)

Then if necessary, HAVING filters based on the reduced data. (HAVING COUNT(*) > 1 for example.)

Then, the SELECT clause chooses, computes, and names, with aliases, the columns to return from the query.

Finally the ORDER BY clause does its sorting operation.

Therefore the alias names and computed column values from your SELECT clause aren't yet in scope when the query planner does its WHERE filtering.

The solution is to nest one query inside another, something like this:

SELECT q.* FROM (
   SELECT a, b, c AS number
     FROM tbl
    WHERE whatever ) q
  WHERE q.number > 2

The alias names or the inner query are in scope for the outer query's WHERE clause.

This kind of query pattern is very common, and the query optimizers handle them as efficiently as possible.

And, you have run afoul of a limitation of the wp_postmeta way of representing every value as a text string. If you want to do date arithmetic on such a value, use STR_TO_DATE() first.

Answer 3

You need to understand how mysql understand the query and how mysql execute it.

https://qxf2.com/blog/mysql-query-execution/

When you execute a SQL query, the order in which the SQL directives get executed is:

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

That said, first mysql will evaluate the FROM and Join clause and then, the WHERE clause where it will filter the data set from the FROM/JOIN clause.

Once the filter is done, it will group the data based on the GROUP BY clause and will retains only thoses satisfying the HAVING clause. After, it evaluate the SELECT clause and order by whatever you want.

For your question, you try to use a field (DeliveryDate) that has been defined in the select clause as a filter.

Try a HAVING MAX(CASE WHEN 'meta_key' = 'value' THEN 'meta_value' ELSE NULL END) >= current_date - interval 7 day instead of putting "DeliveryDate >= current_date - interval 7 day" in the where clause