-1

Suppose i have the following 2 tables:

Persons Table:  
Name  
ID[Primary Key]

Fruits Table:  
Name  
ID[Foreign Key Persons.ID]

This is a table structure for storing persons and the fruits they like. Now if I want to find all the persons who like "Apple" and "Orange"(this would be dynamic). How can i design a SQL query for that?

Nilesh Hirani
  • 43
  • 7
  • 1
    This is a bad design for your problem. You have to design a `many-to-many` relationship – Wajih Jun 07 '16 at 06:00
  • @wajeeh can give details? – Nilesh Hirani Jun 07 '16 at 06:34
  • 1
    You need `Person` and `Fruit` tables, each one having its `primary key`. Then you need `Person_Fruits` table as a join between the previous two tables. This table must have to `Foreign keys` for each table. – Wajih Jun 07 '16 at 06:45

1 Answers1

2

You can use a query like the following to get the IDs of all persons who like Apples and Oranges:

SELECT p.ID
FROM Persons AS p
JOIN Fruits AS f ON p.ID = f.PersonsID
WHERE f.Name IN ('Apple', 'Orange')
GROUP BY p.ID
HAVING COUNT(DISTINCT f.Name) = 2
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98