CODEWITHSUNDEEP
mysqlsql
Jan Dries
Jan Dries

Reputation: 73

MySQL - Finding a distinct value but only if it is true for certain variables

So I'm having the following table closing_days:

id name date kdv_id
1 foo 2021-02-16 1
2 bar 2021-02-16 2
3 baz 2021-02-16 3
4 qux 2021-02-17 3

Id, name, date, kdv_id where kdv_id represents a location.

I want to return a date from closing_day which all locations have in common in a certain period. They told me this could be done by querying only but I just can't seem to find a way to do this.

I have the following at the moment:

SELECT DISTINCT date FROM closing_day
   WHERE kdv_id IN (1, 2, 3)
     AND datum >= '2021-02-15'
     AND datum <= '2021-02-19'

The only problem here is if one of the locations has a date in this period it gets returned. I only want to return a date if all locations have this date as a closing_day.

Going from the sample data: 2021-02-16 and 2021-02-17 would both be returned.

Desired result from sample data is: 2021-02-16

As far as I can see my only option is to select all dates from the table (so not only the distinct ones) and keep track of all records in an array and if the closing day is count(locations) times in the array it is a common closing day. But yeah, that's not doing it by querying only.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You want GROUP BY with a HAVING clause:

SELECT date
FROM closing_day
WHERE kdv_id IN (x, y, z) AND
      datum >= '2021-02-15' AND
      datum <= '2021-02-19'
GROUP BY date
HAVING COUNT(DISTINCT kdv_id) = 3  -- number of items in `IN` list

Upvotes: 1

Related Questions