CODEWITHSUNDEEP
mysql
Ricardo Coelho
Ricardo Coelho

Reputation: 13

MySql - getting the values of a column that are associated with all values from another column

I can't really express it very well but what i need is a query that would allow me to get all elements from a collumn of table A that are associated with all values from a collumn of table B. Here is an example: Theres a table that contains restaurants and foods they have available like this:

restaurant   ||  food
========================
         r1  ||  f1
         r1  ||  f2
         r1  ||  f3
         r2  ||  f1
         r3  ||  f3
         r3  ||  f4

And then theres this table wich contains only a column of foods:

foods
======
f1
f2
f3

What i want is to get the name of all the restaurants that have all the foods from the second table available. In this example the result would be this:

restaurant
==========
r1

I have tried but i am not very experienced and i only know how to get all the restaurants that have foods that appear in the seccond table.

Any help would be aprecciated thanks ;)

Upvotes: 1

Views: 64

Answers (3)

Mihai
Mihai

Reputation: 26784

SQL Fiddle

SELECT restaurant FROM table1  
GROUP BY restaurant 
HAVING COUNT(DISTINCT food)=3;

Replace 3 with the number of distinct foods you have.Or,if you dont want to count manually

SELECT restaurant FROM table1  
GROUP BY restaurant 
HAVING COUNT(DISTINCT food)=(SELECT COUNT(DISTINCT foods) FROM table2 );

SQL Fiddle

Upvotes: 1

Rafael Soufraz
Rafael Soufraz

Reputation: 964

You can use inner join too: Like this:

select a.restaurant from table1 a inner join table2 b on(a.food=b.food)

Upvotes: 0

Haji
Haji

Reputation: 2077

You can use IN in where condition to check 

Select restaurant from table1 where food in (select foods from table2)

If you want to check with specific food from the second table, change the query as follow

Select restaurant from table1 where food = 'f1'

Upvotes: 0

Related Questions