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);

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

select distinct user_id
      ,max(status) over(partition by user_id) as answer
from   user_status
where  product_id in(2,3,4)

Fiddle