CODEWITHSUNDEEP
mysqlsql
Ethan
Ethan

Reputation: 60099

How can I write a MySQL query where two tables have conditions?

Tables...

contracts
---------
id

contract_locations
------------------
id
contract_id     # A contract can have many contract locations.
name            # E.g., "Front office", "Legal", "Contracts admin", etc.
arrival_date

Users consider the location with the last arrival_date to be a given contract's "current location".

They want to be able to find all contracts with a current location name equal to (for example) "Front office" or "Legal".

How can I write a MySQL 5.1 query to do that?

Upvotes: 0

Views: 201

Answers (2)

Barmar
Barmar

Reputation: 780724

SELECT contract_id
FROM contract_locations l
JOIN (SELECT contract_id, MAX(arrival_date) curdate
      FROM contract_locations
      GROUP BY contract_id) m
ON l.contract_id = m.contract_id and l.arrival_date = m.curdate
WHERE l.name = 'Front Office'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

Here is one method:

select c.id
from (select c.*,
             (select name
              from contract_locations cl
              where cl.contract_id = c.id
              order by arrival_date desc
              limit 1
             ) CurrentLocation
      from contracts c
     ) c
where CurrentLocation = 'Front Office'

This uses a correlated subquery to get the current location. Performance will be much improved by having an index on Contract_Locations(contract_id, arrival_date).

Here is another method that may be less obvious. The idea is to see if the most recent date is the most recent date for a given location. This uses a having clause:

select contract_id
from contract_locations cl
group by contract_id
having max(arrival_date) = max(case when name = 'Front Office' then arrival_date end)

The having clause is only true when 'Front Office' (or whatever) is the most recent date.

Upvotes: 1

Related Questions