Check multiple values in a column and return value 1 or 0

user_15294173 0
Viewed 51

I want to return 1 if a user_id have status=1 to any of these product_id 2,3,4 in one MySQL query else return 0

Table: user_status

status_id    user_id    product_id    status
---------------------------------------------
1               27        2             1
2               27        3             0
3               27        4             1
4               32        2             1
5               35        4             1
6               40        2             0
7               40        3             1
8               41        2             0
9               41        3             1
10              45        4             0

I tried the following query,

SELECT status FROM `user_status` WHERE `user_id` = '27'  AND status = '1' AND product_id IN (2,9,11);
mysql sql database
2 Answers

you can use HAVING clause to get the desired results

select user_id from user_status where product_id in (2,3,4) group by user_id having max(status) =1

user_39509460

You can use EXISTS for your purpose, as it return ture (1) or FALSE (0)

CREATE TABLE user_status
    (`status_id` int, `user_id` int, `product_id` int, `status` int)
;
    
INSERT INTO user_status
    (`status_id`, `user_id`, `product_id`, `status`)
VALUES
    (1, 27, 2, 1),
    (2, 27, 3, 0),
    (3, 27, 4, 1),
    (4, 32, 2, 1),
    (5, 35, 4, 1),
    (6, 40, 2, 0),
    (7, 40, 3, 1),
    (8, 41, 2, 0),
    (9, 41, 3, 1),
    (10, 45, 4, 0)
;

SELECT
Exists(SELECT 
    1
FROM
    `user_status`
WHERE
    `user_id` = '27' AND status = '1'
        AND product_id IN (2 , 9, 11)) answer;

1

SELECT
Exists(
SELECT 
    1
FROM
    `user_status`
WHERE
    `user_id` = '49' AND status = '1'
        AND product_id IN (2 , 9, 11)) answer

0

db<>fiddle here

user_51935360
Related