0

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.)

1
  • What do you mean by "distinct rows"? All columns identical? Or some unique key different? – Rick James Jun 10 '20 at 21:11
0

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)

0

using sub-queries

select off.id,
       off.name,
       off.phone,
       a.street_address,
       a.zip,
       a.city
from office as off
         inner join (select m1.office_id
                     from meeting as m1
                     where (date(m1.meeting_date) between '2020-02-01' and '2020-02-29')
                       and m1.allowed_lunch = 0) as feb
                    on feb.office_id = off.id
         inner join address as a
                    on off.address_id = a.id
where off.id in (select m2.office_id
                         from meeting as m2
                         where (date(m2.meeting_date) between '2020-01-01' and '2020-01-30')
                           and m2.allowed_lunch = 1)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.