SELECT data with multiple rows using the same id based on 3 columns

Question

I have a table Data which contains some data. Several rows belong to the same customer having the same customer_id (index to Customers table). This customer has answered some questions which they have question_id (index to Questions table). In the Data table, there is also the value column regarding the answer of the customer to question0

I am trying to select the customer_id for which the answers to question_id 1, 2, 3 are 0

I have also tried

SELECT distinct customer_id
FROM Data
WHERE value = 0
AND question_id IN (1, 2, 3);

but I am not getting the appropriate result which should be 24 which is the customer_id of the customer that has answered 0 to the questions with question_id 1, 2 and 3.

Specify the expected result as well. (Having the above table data.) BTW, most people here want formatted text, not images. — jarlh
– jarlh, Commented Jan 17, 2018 at 13:22
Both answers given by the Wanderer and Gordon Linoff are correct. — vchan
– vchan, Commented Jan 30, 2018 at 9:18

Gordon Linoff · Accepted Answer · 2018-01-17 13:23:17Z

2

You are close. You can get what you want using group by and having:

SELECT customer_id
FROM Data
WHERE value = 0 AND question_id IN (1, 2, 3)
GROUP BY customer_id
HAVING COUNT(DISTINCT question_id) = 3;

The HAVING clause specifies that all three questions are answered -- and that the answers are 0.

answered Jan 17, 2018 at 13:23

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ullas · Accepted Answer · 2018-01-17 13:37:02Z

1

An alternative way with GROUP BY and HAVING.

Query

select `customer_id`
from `your_table_name`
where `question_id` in (1,2,3)
group by `customer_id`
having sum(`value`) = 0;

Find a demo here

answered Jan 17, 2018 at 13:37

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

Collectives™ on Stack Overflow

SELECT data with multiple rows using the same id based on 3 columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related