I'm trying to figure out a query. I have an
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.)