CODEWITHSUNDEEP
mysql
Megamind Saiko
Megamind Saiko

Reputation: 697

MySQL - Many to Many relationship - tables

In a MySQL database, We have three tables: 'Answers', 'Items' and 'AnswerItems' that links between the first two tables.

Answers Table

AnswerID

(and other columns)

===================

Items Table

ItemID

(and other columns)

==================

AnswerItems Table

AnswerID

ItemID

===============

now, we want to get answers that have certain items (not more or less)

we tried this (assuming we are searching for answers having items with IDs: FirstItemID and SecondItemID)

select AnswerID
from AnswerItems
where ItemID in (FirstItemID,SecondItemID) 
group by AnswerID
having COUNT(distinct ItemID) = 2

this query also returns answers that have other items with the selected two items above

SQL Fiddle: http://sqlfiddle.com/#!2/d9124/3

Upvotes: 0

Views: 57

Answers (2)

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

You can try below

SELECT * FROM AnswerItems
where ItemID in (1,2)
group by Answerid having count(*) = 2

Upvotes: 0

Jason Heo
Jason Heo

Reputation: 10236

To find AnswerID has Only ItemID 1 and 2:

SELECT *
FROM AnswerItems t1 INNER JOIN (
    SELECT AnswerID
    FROM AnswerItems
    WHERE ItemID in (1, 2)
    GROUP BY AnswerID
    HAVING COUNT(DISTINCT ItemID) = 2
) t2 ON t1.AnswerID = t2.AnswerID
GROUP BY t1.AnswerID
HAVING COUNT(*) = 2;

Upvotes: 1

Related Questions