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 have to do is get any food that belongs to more than one person. Now I have to do it with COUNT and WHERE. You might need to do an INNER JOIN.
It should be easy, but I just can't see it.
+3
VanZan
source
to share
3 answers
You want to join tables in a common field. WHERE serves as an internal connection. Then GROUP BY Foods.name concatenates the rows by food type. Count is an aggregated operator that always works with GROUP BY.
SELECT Foods.Name, Count(*)
FROM Foods, People
WHERE Foods.ID = People.Food
GROUP BY Foods.Name
HAVING COUNT(*)>1;
Omit the first counter (*) if you only want a list of products.
+1
Joshua R.
source
to share
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
+1
johnny
source
to share
select * from food inner join people on people.id = food.id group by people.name
-1
Marcos Vinicius de Souza Gouve
source
to share