Mysql – Selecting distinct rows from two tables, using multiple conditions

MySQLquerysubquery

I'm trying to figure out a query. I have an offices and meetings table in my DB. Offices can have multiple meetings (office ID is a FK to the offices table). Some meetings allow lunch, some do not. I'm trying to find a list of all offices with meetings in January that allowed lunch, which also had meetings in February that did NOT allow lunch.

My sanitized schema looks something like this:

+---------------+   +---------------+
| meetings      |   | offices       |
+---------------+   +---------------+
| id            |   | id            |
| meeting_date  |   | name          |
| allowed_lunch |   | address_id    |
| office_id     |   | phone         |
+---------------+   +---------------+

I want a list with office id's, names, phone numbers, as well as some information from other tables I need to join to, such as street address, zip, state, etc. So far I've only been able to come up with a clunky way of doing this; I use two queries.

The first query gets a distinct list of office ID's, using a subquery:

select distinct offices.id from offices
join meetings on offices.id = meetings.office_id
where offices.id in (
  select
    distinct offices.id
  from offices
  join meetings on meetings.office_id = offices.id
  where 
    DATE(meeting_date) < '2020-01-31' and DATE(meeting_date) >= '2020-01-01' and allowed_lunch = 1
)
and DATE(meeting_date) < '2020-02-28' and DATE(meeting_date) >= '2020-02-01' and allowed_lunch = 0;

I then manually take that list of office id's and look them up again in a separate query to pull the additional information I need from other tables. So something like this:

SELECT office.name, office.phone, ..., address.zip, address.state
FROM offices
JOIN addresses on offices.address_id = addresses.id
WHERE office.id in (
... big list from first query ...
);

What I need is a distinct list of offices, which satisfy the two conditions listed at the top.
Is there a better way to go about doing this? Preferably within a single query?

(I could take the first query and stick it under the WHERE clause in the second query. I had performance issues doing this though. The first query takes about 10 seconds, and the second query is pretty quick, but when I combine them in an additional subquery, it becomes very slow. Plus it seems a rather messy way to handle it.)

Best Answer

Schematically:

SELECT offices.id
-- join tables
FROM offices
JOIN meetings ON offices.id = meetings.office_id
-- select only rows for Jan and Feb 2020
WHERE DATE(meeting_date) BETWEEN '2020-01-01' AND '2020-02-29'
-- grouping - prepare calculations for separate offices
GROUP BY offices.id
-- check that the amount of `allowed_lunch` in Feb is zero
HAVING NOT SUM(DATE(meeting_date) BETWEEN '2020-02-01' AND '2020-02-29' AND allowed_lunch)
-- but total is not zero (taking into account prev. check - they're in Jan)
   AND SUM(allowed_lunch)
Related Question