-1

Can anyone tell me why this statement is not limiting the results to today and the last 7 days? it is showing me results from 3 months ago along with results from today. In a different post I was told I had to change from a WHERE clause to a HAVING clause. Is it related to the variable in the %trans%?

SELECT a.`post_id`, b.`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 

FROM table_1 a
INNER JOIN table_2 b ON FIND_IN_SET(a.post_id, b.payment_ids) 
GROUP BY a.post_id 
HAVING DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AND DeliveryType = 'pickup' AND  OrderStatus = 'ready' OR OrderStatus = 'transit'  
ORDER BY 'DeliveryTime'  DESC
Tim
  • 17
  • 6
  • First, you should probably learn what `HAVING` is used for. That's definitely not how you use `HAVING`. – Eric Aug 17 '20 at 23:57
  • Before that, probably a good idea to learn `GROUP BY` first, then `HAVING`. Your `GROUP BY` also doesn't make sense. – Eric Aug 17 '20 at 23:59
  • I had WHERE first and was told that was not what WHERE was for in this post. https://stackoverflow.com/questions/63432930/where-clause-great-than-zero-still-showing-zero-when-using-case-functions/63432952#63432952 WHERE returns blank results when properly used – Tim Aug 18 '20 at 00:05
  • 1
    Please describe what you actually want to do. Post a sample input, expected output. Without those information, no one could help you. – Eric Aug 18 '20 at 00:13
  • I need it to display results that equal the HAVING conditions. The answer below makes sense to me but when I put it in quotes, it only displays the three month old result instead of todays results. I want it to only display results from the past 7 days – Tim Aug 18 '20 at 00:24
  • In the answer below, it picks up records which DeliveryDate is 7 days ago. If you want to make it the past 7 days, just modify it. `DeliveryDate >= (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AND DeliveryDate <= CURDATE()`. – Eric Aug 18 '20 at 00:37
  • Thanks. If you can see below, youll see that it is not working as designed below. When adding () around the OR clause it ONLY shows the 3 month old record. Not the record from today which is all its supposed to be showing. – Tim Aug 18 '20 at 01:02
  • 1
    Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. – philipxy Aug 18 '20 at 04:56
  • Possible duplicate of [SQL Logic Operator Precedence: And and Or](https://stackoverflow.com/q/1241142/3404097) – philipxy Aug 18 '20 at 05:02
  • @philipy thanks. This was suggested by someone else to duplicate the logic on the HAVING clause so I tried it since they were helping. – Tim Aug 18 '20 at 15:31

1 Answers1

1

This is your code:

DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) 
AND DeliveryType = 'pickup' 
AND  OrderStatus = 'ready' 
OR OrderStatus = 'transit'

You have a logical precedence issue. OR has lower precedence than AND, so this reads as:

(
    DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) 
    AND DeliveryType = 'pickup' 
    AND  OrderStatus = 'ready' 
)
OR OrderStatus = 'transit'

Now it is plain to see that this allows any row in "transit" status regardless of its delivery type or date.

You need parentheses around the OR condition - or simpler yet, use IN:

DeliveryDate = (DATE_SUB(CURDATE(), INTERVAL 7 DAY)) 
AND DeliveryType = 'pickup' 
AND OrderStatus IN ('ready', 'transit')

Notes:

  • it would make more sense to put these conditions in the WHERE rather than in the HAVING clause, since they do not operate on aggregated columns

  • DATE_SUB(CURDATE(), INTERVAL 7 DAY) can be simplified as current_date - interval 7 day

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
GMB
  • 216,147
  • 25
  • 84
  • 135
  • Thank you. The GROUP BY is only for post_id. The HAVING clause is beyond that. Cant seem to use a WHERE clause because it gives unknown column errors. I tried using the IN condition but it provides a blank result return. That makes sense about the OR being a lesser condition. – Tim Aug 17 '20 at 23:50
  • Thank you. The GROUP BY is only for post_id. The HAVING clause is beyond that. Cant seem to use a WHERE clause because it gives unknown column errors. I tried using the IN condition but it provides a blank result return. That makes sense about the OR being a lesser condition. Adding () around the OR condition only gives the 3 month old result? Did I do it wrong? (OrderStatus = 'transit') – Tim Aug 17 '20 at 23:56
  • @Tim . . . Unless `post_id` is the primary key of `a` and `deliverydate` and `status` are both in `a`, then your query is malformed and should return an error. – Gordon Linoff Aug 18 '20 at 01:06
  • @GordonLinoff: I suspect that this is an alias defined in the `select` clause, but that Tim removed when preparing their example query. If so, then the predicates on these columns should indeed belong to the `HAVING` clause. – GMB Aug 18 '20 at 01:07
  • @GordonLindoff Thanks. a. and b. is what is being used as a solution to the FIND-IN_SET in the INNER JOIN. It returns results ok and didnt become a problem until I added the OR clause – Tim Aug 18 '20 at 01:14
  • @gordonlinoff See now I am getting confused. I posted this and got this answer about what you had mentioned above. I am not mentioning this to embarrass you but to try and understand. https://stackoverflow.com/questions/63460464/having-vs-where-vs-group-by-clauses-when-to-use-them-and-if-you-use/63460578#63460578 – Tim Aug 18 '20 at 01:35