MySQL Self Join with a specific condition

Question

My Question is, I have a table which is relatively big (~2 millions rows).

Table is like ;

id    Action   user_id        Date
-----------------------------------
1      FOP      userx        date1
2      POP      userx        date2
3      FOP      userx         NULL 
4      FOP      usery        date4
5      POP      usery        date5
6      FOP      userz        date6
7      POP      userz        date7
8      FOP      userz         NULL
9      FOP      usert        date9
10     POP      usert        date10
11     FOP      usert        date11
12     POP      usert        date12

In table, if there is FOP and no date (Date=NULL), it means user is active. I'm looking for non-active users so I dont want to to have a users who has a FOP with a date=NULL. If there is POP, we must have a date.

Specifically, in my case number of FOP should be equal to number of POP.

For exemple : In our case, I just want to get usery and usert's informations.

I didn't efficiently do join for this table. Because table is really big.

Thanks for your ideas.

score 1 · Accepted Answer · answered Apr 05 '17 at 09:59

1

This should do it

SELECT *
FROM table
WHERE action = POP
AND user_id NOT IN (SELECT user_id FROM table WHEREdate = NULL)

answered Apr 05 '17 at 09:59

Shogunivar

1,237
11
18

@KemalDiri No problem, If this answer was the solution to your problem, please consider accepting it as the correct answer – Shogunivar Apr 05 '17 at 10:07

score 0 · Answer 2 · answered Apr 05 '17 at 09:57

0

You need some code to get all info from x but in background so your mind ?

answered Apr 05 '17 at 09:57

Shabi Levi

248
1
2
13

MySQL Self Join with a specific condition

2 Answers2