I have 3 tables. They are actually different but I will make up 3 simple tables to show my problem:
1 Apples, 1
2 Pears, 1
3 Potatoes, 2
I want to select a count of the food types:
There should be 3 from the FoodType table.
I want to return how many FoodTypeIds are in the Eat table by count. The Eat table doesn't contain a FoodtypeId, but does contain the FoodId which has the FoodtypeId.
In my example, the results I need to return are these (but I will actually return the FoodTypeId, not the FoodName):
Fruit = 2
Vegetables = 1
Meat = 0
I tried this but it is a dismal failure:
select count(food.footypeid) as typecount,
left outer join Eat on food.foodId = Eat.foodId
group by food.foodTypeid
order by count(food.foodTypeid)