CODEWITHSUNDEEP
mysqlcountwhere-clause
VanZan
VanZan

Reputation: 65

Using Where and Count on two tables

I have two tables

Foods
----------
ID    Name
----------
1. Chips
2. Pizza
3. Fish
4. Pasta

People
-----------------
ID Name    FoodID
-----------------
1. Robert       3
2. Norman       2
3. Leonard      4
4. Huey         3

What I'm supposed to do is retrieve any food that belongs to more than one person. Now I'm supposed to do this by means of COUNT and WHERE. Possibly might need to do an INNER JOIN.

It should be simple but I'm just not seeing it.

Upvotes: 3

Views: 55

Answers (3)

Joshua R.
Joshua R.

Reputation: 2302

You want to join the tables on the common field. The WHERE is serving as an inner join. Then GROUP BY Foods.name aggregate rows by food type. Count is an aggregate operator that always works with the GROUP BY.

SELECT Foods.Name, Count(*)
FROM Foods, People
WHERE Foods.ID = People.Food
GROUP BY Foods.Name
HAVING COUNT(*)>1;

Omit the first count(*) if you just want the list of foods.

Upvotes: 1

johnny
johnny

Reputation: 19735

select f.name, p.name, count(f.id) as total from foods f, people p 
where (f.id = p.FoodID)
group by f.name
having total > 1
order by f.name

http://sqlfiddle.com/#!9/b2a63/10

Upvotes: 1

Marcos Vinicius de Souza Gouve
Marcos Vinicius de Souza Gouve

Reputation: 13

select * from food inner join people  on people.id = food.id group by people.name

Upvotes: -1

Related Questions