Intersect multiple rows of the table based on conditions

Question

I have a table with the columns response_id, question_id and answer_id.

I would like to find all response_id matching multiple conditions. For example the following are some usecases

User selected answer 1 for question 1 and answer 2 of question 2
User (selected answer 3 for question 1 and answer 2 for question 2) or answer 1 for question 3

In SQL, I can do this using INTERSECT and UNION but the INTERSECT is not available in MySQL. Can someone guide me how to solve this problem?

example in SQL, which is needed in MySQL.

select distinct(response_id) from table where question_id = 873 AND answer_id = 5269
intersect
select distinct(response_id) from table where question_id = 874 AND answer_id = 5273
intersect
select distinct(response_id) from table where question_id = 877 AND answer_id = 5286

Not my downvote, but your question is not very clear. Can you at least show us some data which explains what you are trying to do? — Tim Biegeleisen, Apr 22 '18 at 13:50

score 1 · Accepted Answer · answered Apr 22 '18 at 14:02

MySQL does not support INTERSECT, but we may simulate it using an EXISTS clause:

SELECT DISTINCT response_id
FROM table
WHERE
    question_id = 873 AND
    answer_id = 5269 AND
    response_id IN (
        SELECT DISTINCT response_id FROM yourTable
        WHERE question_id = 874 AND answer_id = 5273) AND
    response_id IN (
        SELECT DISTINCT response_id FROM yourTable
        WHERE question_id = 877 AND answer_id = 5286);

Anay Pareek · Answer 2 · 2018-04-22T14:12:59.123

0

SELECT DISTINCT * FROM 
(SELECT table.response_id FROM table, data WHERE table.question_id = 873 AND  
table.answer_id = 5269) query1
INNER JOIN
(SELECT table.response_id FROM table, data WHERE table.question_id =874 AND 
table.answer_id = 5273) query2

edited Apr 22 '18 at 14:12

answered Apr 22 '18 at 13:53

Anay Pareek

121
1
8

Union gives combination of rows matching in all queries, I want intersection – Nagarjun Apr 22 '18 at 13:55
then maybe something like this SELECT DISTINCT records.id FROM records WHERE records.firstname IN ( select id from data where value = 'john' ) AND records.lastname IN ( select id from data where value = 'smith' ) – Anay Pareek Apr 22 '18 at 14:04

Intersect multiple rows of the table based on conditions

2 Answers2