There is a table of users with fields date_created, contract_type .

I need to withdraw in the request of all users who have expired contract.

You need to do a check on the field contract_type , which can have two values: M2M - 30 days, Annual - year.

How to add conditions for contract types to this request and check if the contract has expired?

 SELECT u.id, u.dateCreated, DATE(NOW()) as date_now, c.name FROM users u JOIN contract_type c ON u.contract_type = c.id WHERE u.contract_type is not null

Own attempt of the decision:

It seems that he himself thought up something, only he swears at CONCAT, tell me where the error is. Thank you, of course, for the right answer, I just wanted to try to solve the problem myself too.

 SELECT u.id, u.email, u.dateCreated, DATE(NOW()) as date_now, c.name, CASE c.name WHEN "M2M" THEN 'MONTH' ELSE "YEAR" END as type_value FROM users u JOIN contract_type c ON u.contract_type = c.id HAVING date_now > u.dateCreated + INTERVAL CONCAT("1", type_value)

    1 answer 1

    checking u.contract_type is not null - redundant, since you are join over this field.

    for example, you can do this:

    SQL feeddle

    MySQL 5.6 Schema Setup :

     create table u (u int, d date, cid int); insert into u values (1, '2015-01-01', 1) ,(2, '2015-01-01', 2) ,(3, '2014-01-01', 1) ,(4, '2015-08-01', 2) ,(5, '2014-01-01', null) ; create table c (cid int, t text); insert into c values (1, 'annual') ,(2, 'm2m') ;

    Query 1 :

     select uu, ud, ct from u join c on c.cid = u.cid where ct = 'annual' and ud < date_sub(now(), interval 1 year) or ct = 'm2m' and ud < date_sub(now(), interval 30 day)

    Results :

     | u | d | t | |---|---------------------------|--------| | 2 | January, 01 2015 00:00:00 | m2m | | 3 | January, 01 2014 00:00:00 | annual |
    • Testing for Null is not completely superfluous, since there can be users without a contract in the database. - quaresma89
    • in simple terms, join already "includes" this check. - aleksandr barakin