Selecting store with multiple products

Question

I have a table named store and one named products.

The data is structured like this:

Store table 
ID NAME
1, 'red fruit store';
2, 'blue fruit store';
3, 'orange fruit store';

Fruits table
ID STORE_ID PRODUCT_NAME
1, 1, 'orange';
2, 1, 'apple';
3, 1, 'banana';
4, 2, 'apple';
5, 3, 'banana';
6, 3, 'pear';

Vegetables table
ID STORE_ID PRODUCT_NAME
1, 1, 'tomato';
2, 1, 'carrot';
3, 1, 'potato';
4, 2, 'cabbage';
5, 3, 'tomato';
6, 3, 'carrot';

So I want to select the store that has both orange and banana from the fruits table and tomato, carrot and potato from the vegetables table.

How can I do that?

EDIT

If I want to select the store having

• oranges
• one or multiple from (banana, apple pear)
• and tomato or carrot

How can I do that ?

Answer 1

You need a join between the tables, a condition on the results like:

product_name in ('banana', 'orange')

and then group by s.id, s.name to get only the stores that have both 'banana' and 'orange':

select s.id, s.name 
from store s inner join product p
on p.store_id = s.id
where p.product_name in ('banana', 'orange')
group by s.id, s.name
having count(*) = 2

If you need to check for more products you will have to change the list that is checked with in and the number of having count(*) = ? to be exactly the number of items in the list.
Another way to get the results you want is with EXISTS:

select s.* from store s
where 
  exists (
    select 1 from products
    where store_id = s.id and product_name = 'orange'
  )
  and
  exists (
    select 1 from products
    where store_id = s.id and product_name = 'banana'
  )

Edit
For your edited question:

select s.id, s.name 
from store s
inner join fruits f on f.store_id = s.id
inner join vegetables v on v.store_id = s.id
where 
  f.product_name in ('banana', 'orange')
  and
  v.product_name in ('tomato', 'carrot', 'potato')
group by s.id, s.name
having 
  count(distinct f.id) = 2
  and 
  count(distinct v.id) = 3

See the demo.
Results:

| id  | name            |
| --- | --------------- |
| 1   | red fruit store |

Answer 2

If you want the store that have both the products (at least) you could use a couple of inner join

 SELECT s.id AS store_id, 
         s.name AS store_name
  FROM store AS s 
  INNER JOIN Products p1 ON p1.STORE_ID = s.ID AND  p1.PRODUCT_NAME = 'orange' 
  INNER JOIN Products p2 ON p2.STORE_ID = s.ID AND  p2.PRODUCT_NAME = 'banana' 

You query fail because there not row with both the product so the AND fail

if you want the store with al least one of the two product you can use left join

or you could use count of product group by store

SELECT s.id AS store_id, 
       s.name AS store_name
FROM store AS s 
INNER JOIN (
  select  STORE_ID 
  from Products where PRODUCT_NAME IN ('orange','banana' )
  group by STORE_ID  
  having count(distinct PRODUCT_NAME ) = 2
)