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.