CODEWITHSUNDEEP
mysqlsqldatabase
Joseph Martínez
Joseph Martínez

Reputation: 57

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

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

Upvotes: 2

Views: 70

Answers (2)

nbk
nbk

Reputation: 49385

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

Upvotes: 2

radar
radar

Reputation: 13425

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

Upvotes: 2

Related Questions