MySQL - SQL select query with two tables using where, count and having

0
votes

There are two tables: client and contract.

client table:

client_code INT pk
status      VARCHAR

A client can have 1 or more contracts. The client has a status column which specifies if it has valid contracts - the values are 'active' or 'inactive'. The contract is specified for a client with active status.

contract table:

contract_code INT pk
client_code   INT pk
end_date      DATE

A contract has an end date. A contract end date before today is an expired contract.

REQUIREMENT: A report requires all active clients with contracts, but with all (not some) contracts having expired date. Some example data is shown below:

Client data:

client_code status
----------------------------------
1           active
2           inactive
3           active
4           active

Contract data:

contract_code   client_code   end_date
-------------------------------------------------------------
11              1             08-12-2018    
12              1             09-12-2018
13              1             10-12-2018
31              3             11-31-2018
32              3             10-30-2018
41              4             01-31-2019
42              4             12-31-2018

Expected result:

client_code
-------------
1

RESULT: This client (client_code = 1) has all contracts with expired dates: 08-12-2018, 09-12-2018 and 10-12-2018.

I need some help to write a SQL query to get this result. I am not sure what constructs I have to use - one can point out what I can try. The database is MySQL 5.5.

2
mysqlsqlselect

2 Answers

0
votes

One approach uses aggregation. We can join together the client and contract tables, then aggregate by client, checking that, for an active client, there exist no contract end dates which occur in the future.

SELECT
    c.client_code
FROM client c
INNER JOIN contract co
    ON c.client_code = co.client_code
WHERE
    c.status = 'active'
GROUP BY
    c.client_code
HAVING
    SUM(CASE WHEN co.end_date > CURDATE() THEN 1 ELSE 0 END) = 0;

Demo

Note: I am assuming that your dates are appearing in M-D-Y format simply due to the particular formatting, and that end_date is actually a proper date column. If instead you are storing your dates as text, then we might have to make a call to STR_TO_DATE to convert them to dates first.

0
votes

Is that what you're looking for?

select clients.client_code
from clients
join contracts
on contracts.client_code=clients.client_code
where status='active'
group by clients.client_code
having min(end_date)>curdate()