Mysql selection of related data

Question

Continuing the question: Checking the text condition in the request

There is a table of users, and each of them, in turn, may issue a license to another user.

As a result, in the database there are the following fields:

Id, created_by, date_created, contract_type

I want to select all users who have expired license, and those users who have been licensed by this user. If the license for the user who issued the license expires, it will automatically expire for all those who received it.

At the moment there is such a variant of the request, but it does not display what you need:

SELECT u.id,u.email,u.dateCreated,c.name,u.created_by FROM users u JOIN contract_type c ON u.contract_type = c.id WHERE( c.name = 'M2M' AND u.dateCreated < date_sub(NOW(),INTERVAL 1 MONTH) OR c.name = 'Annual' AND u.dateCreated < date_sub(NOW(),INTERVAL 1 YEAR)) AND u.created_by = u.id

How to change this request so that users who have received a license from a user with a expired license are displayed?

Accepted Answer · 2015-08-27T07:01:34

so add the appropriate condition:

 SELECT u.id,u.email,u.dateCreated,c.name FROM users u JOIN contract_type c ON u.contract_type = c.id WHERE ( c.name = 'M2M' AND u.dateCreated < date_sub(NOW(),INTERVAL 1 MONTH) OR c.name = 'Annual' AND u.dateCreated < date_sub(NOW(),INTERVAL 1 YEAR) ) and u.created_by = номер

example on sqlfiddle. the second query selects only those who have created_by equal to 1 , the third query selects all who have either their own license expired or the creator's license:

SQL feeddle

MySQL 5.6 Schema Setup :

 create table u (u int, d date, cid int, created_by int); insert into u values (1, '2015-01-01', 1, null) ,(2, '2015-01-01', 2, null) ,(3, '2015-01-01', 1, 1) ,(4, '2015-01-01', 2, 1) ,(5, '2015-08-01', 1, 2) ,(6, '2015-07-01', 2, 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 | | 4 | January, 01 2015 00:00:00 | m2m | | 6 | July, 01 2015 00:00:00 | m2m |

Query 2 :

 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) ) and u.created_by = 1

Results :

 | u | d | t | |---|---------------------------|-----| | 4 | January, 01 2015 00:00:00 | m2m |

Query 3 :

 select u1.u, u1.d, c1.t, u2.d as creator_date, c2.t as creator_type from u u1 join c c1 on c1.cid = u1.cid left join u u2 on u1.created_by = u2.u left join c c2 on c2.cid = u2.cid where c1.t = 'annual' and u1.d < date_sub(now(), interval 1 year) or c1.t = 'm2m' and u1.d < date_sub(now(), interval 30 day) or c2.t = 'annual' and u2.d < date_sub(now(), interval 1 year) or c2.t = 'm2m' and u2.d < date_sub(now(), interval 30 day) order by 1

Results :

 | u | d | t | d | t | |---|---------------------------|--------|---------------------------|--------| | 2 | January, 01 2015 00:00:00 | m2m | (null) | (null) | | 4 | January, 01 2015 00:00:00 | m2m | January, 01 2015 00:00:00 | annual | | 5 | August, 01 2015 00:00:00 | annual | January, 01 2015 00:00:00 | m2m | | 6 | July, 01 2015 00:00:00 | m2m | (null) | (null) |

that I choose a record that meets 2 conditions, if one does not pass, the record is not selected
It turns out first you need to select all users whose license has expired, and then, based on these users, select those users who were granted a license!
The answer is updated - added the same request that offered to you in response to yesterday's question, adding it with a query with the selection of only those users who have created_by equal to 1
I do not quite understand why the last request brought out one user, in theory it should be 3, the user himself who purchased the license and 2 others who issued it, right?

Mysql selection of related data

1 answer 1

More articles: