How to make selection from MySql database according to the condition — only if all records have a value?

There is a table "order":

id | status_id | user_id
1 | 0 | 1
2 | 1 | 1
3 | 0 | 2
4 | 0 | 2
5 | 0 | 3
6 | 1 | 4

You should print all user_id who have ALL of the orders with a status of 0, that is, the result should be:
2 and 3
Or only with a status of 1, then the result should be:
4
June 14th 19 at 20:20
4 answers
June 14th 19 at 20:22
Simple, but probably not the best option.
SELECT * FROM table GROUP BY user_id HAVING SUM (status_id) = 0
And if you want to display all the only with the status 4, your version will not work - kelsi.Lango commented on June 14th 19 at 20:25
naturally.
but the issue
You should print all user_id who have ALL of the orders with a status of 0,


there are other aggregious functions, such as AVG - marcus.Wel commented on June 14th 19 at 20:28
If there is a negative status_id, this method also will not work. - Francisco.Wisoky commented on June 14th 19 at 20:31
, ,
SELECT user_id 
FROM table
GROUP BY user_id
HAVING SUM(ABS(status_id - 4)) = 0;
- Benedict_Shiel commented on June 14th 19 at 20:34
June 14th 19 at 20:24
Gospodi:
select user_id from order where status_id = '0';

or

select user_id from order where status_id = '1';
You do not understand - it is necessary only if ALL of the orders that status, if at least one other the user will not be displayed.
In your example 1,2,2,3 and 1.4 - kelsi.Lango commented on June 14th 19 at 20:27
Yes , not understand, I apologize, then the option of HAVING your. - marcus.Wel commented on June 14th 19 at 20:30
June 14th 19 at 20:26
SELECT user_id FROM table
GROUP BY user_id
HAVING SUM (ABS(status_id)) -COUNT(status_id)=0
or
HAVING SUM (ABS(status_id)) = COUNT(status_id)
Displays 2,3,4
June 14th 19 at 20:28
Something like this:
select 
distinct
t1.user_id
from table t1
left join 
 (select distinct 
 user_id 
 from table 
 where status_id <> 0
 ) t2 on t2.user_id = t1.user_id
where t1.status_id = 0
and t2.user_id is null
Checked, my query works with any status_id. - kelsi.Lango commented on June 14th 19 at 20:31

Find more questions by tags SQLMySQL