jeudi 23 juin 2016

Get all distinct users - MySql

I have a table contains the following type and information. product_id | user_id | product | date_opened 1 | 10 | shoes | 2016-04-01 2 | 26 | shoes | 2016-04-01 3 | 10 | watch | 2016-04-01 4 | 23 | shoes | 2016-04-01 5 | 10 | shoes | 2016-01-01 6 | 13 | watch | 2016-01-01 7 | 14 | shoes | 2015-11-02 8 | 10 | slippers| 2015-11-02 9 | 10 | shoes | 2015-11-02 10 | 15 | watch | 2015-11-02 11 | 19 | watch | 2015-09-03 12 | 19 | watch | 2015-03-02 13 | 19 | shoes | 2015-01-03 Users can buy products if it is open. date_open is the date cycle. users can buy as many product as he wants, example is user_id 10, user 10 have 2 products on cycle 2016-04-01. One product on 2016-01-01, two on 2015-11-02. Now, I would like to get all the (distinct) user_id who are active / have products on all previous 3 cycle dates (2016-04-01, 2016-01-01 and 2015-11-02). Note that a user can have many products in a cycle. Additional: Active - should consecutively have products in 3 set dates and without skipping. So it should show all user with products in date 2016-04-01 and 2016-01-01 and 2015-11-02. Not user with products only in 1 or 2 dates. Another problem here: Example, I have users with different dates (2000-04-05 , 2001-09-03, 2006-09-01, 2015-11-02 and so on..) I just want to get here are all those user with dates ('2016-04-01', '2016-01-01', '2015-11-02').

Aucun commentaire:

Enregistrer un commentaire