MySQL linking table intersection query

Question

Sorry if my question sounds misleading, I'm not fully sure how to formulate it.

Consider the following tables: Fruit which has an ID and Name, Person which also has an ID and Name, and Person_has_Fruit (many to many linking table) which has a Person_ID and a Fruit_ID.

What transaction can retrieve the people which have two or more specific fruits? Basically how can I intersect the results?

Example:

Fruit table

ID | Name
-----------------
1  | Apple
2  | Pineapple
3  | Banana
4  | Lemon

Person table

ID | Name
-----------------
1  | Tom
2  | Bill
3  | John

Many to many table

PersonID | FruitID
-----------------
    3   |    1
    1   |    2
    3   |    2
    2   |    3
    3   |    3

I want a query to retrieve me John when I ask for the person which has Apple, Pineapple and Banana. Any suggestions?

I've tried 'SELECT * FROM Person_has_Fruit WHERE FruitID in ('1', '2', '3')' but that is incorrect as it retrieves all the person IDs which have one of them, so basically it implies an or relationship between the values.

possible duplicate of [How to return rows that have the same column values in MySql](http://stackoverflow.com/questions/16704290/how-to-return-rows-that-have-the-same-column-values-in-mysql) — Barmar, Apr 19 '14 at 20:01
Found this from Google, the definitive Stack Overflow question on this concept, intersection of tables/"relational division" is here: http://stackoverflow.com/questions/7364969/how-to-filter-sql-results-in-a-has-many-through-relation — Eric L., Oct 29 '14 at 18:08

score 1 · Accepted Answer · answered Apr 19 '14 at 20:23

You are looking to perform a relational division between the some tuples of Fruit table and the Person table.

So:

Select * From Person p
Where Not Exists ( Select * from Fruit f 
                   Where (Name = 'Apple' Or Name = 'Pinneapple' or Name = 'Banana')
                   And Not Exists ( Select * from Person_has_Fruit pf
                                    Where pf.PersonId = p.ID and pf.FruitId=f.ID))

el3ien · Answer 2 · 2014-04-19T23:58:04.677

0

Try create a view first.

CREATE VIEW fruitowners AS 
( SELECT personfruit.personID, fruittable.id, persontable.person, fruittable.fruit
FROM persontable, fruittable, personfruit
WHERE personfruit.personID=persontable.id AND personfruit.fruitID=fruittable.id )

Then:

SELECT fruit FROM fruitowners
WHERE person='John'

returns all the fruit types John owns

And:

SELECT person FROM fruitowners
WHERE fruit='Banana'

returns all the banana owners.

And:

SELECT COUNT(*) FROM fruitowners WHERE person='John'

returns the number of fruits John owns

And:

SELECT COUNT('fruit') FROM fruitowners WHERE person='John' and fruit='Banana'

returns how many bananas John owns

edited Apr 19 '14 at 23:58

answered Apr 19 '14 at 21:05

el3ien

5,362
1
17
33

1

Your suggestion just allows to know which person has a certain type of fruits, or which fruit, a certain person has. With your answer, it's not possible to know if a person has 2 types of fruits without extra computation. – g_tec Apr 19 '14 at 23:17
Yes the (SELECT fruit FROM fruitowners WHERE person='John') returns all the fruit types john owns – el3ien Apr 19 '14 at 23:40
1

It's true but than you have to check if (as on the example provided on the question: apple, pineapple, banana) certain fruits are on the returned rows. – g_tec Apr 19 '14 at 23:43
1

Everything you said it's true. But the question is to discover which person has certain types of fruits. With your view, answer to the query "Which person has Pineapple and has Banana". – g_tec Apr 20 '14 at 00:08
You mean like SELECT DISTINCT person FROM fruitowners WHERE fruit='Banana' OR fruit='Pineapple' ? This would return the names of those who own those two fruit types – el3ien Apr 20 '14 at 00:23
This will find persons that has banana or pineapple. If a person just have one, it will be caught by your query. It's suppose to retrieve Just the persons that have Both fruits, and not just one. – g_tec Apr 20 '14 at 00:32
No! Replacing the OR by an AND, it's a Huge mistake, It's not possible for a row to have 2 values for the same attribute(column). – g_tec Apr 20 '14 at 01:11

MySQL linking table intersection query

2 Answers2