Select Query with Counts Help

I have 3 tables. They are actually different but I will make up 3 simple tables to show my problem:

Table 1:
FoodType
FoodTypeId
FoodTypeName

1 Fruit
2 Vegetables
3 Meat

Table 2:
Food
FoodId
FoodName
FoodTypeId

1 Apples, 1
2 Pears, 1
3 Potatoes, 2


Table 3:
Eat
EatId
FoodId

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

Any ideas?

I tried this but it is a dismal failure:
select count(food.footypeid) as typecount, 
food.footypeid
from food
left outer join Eat on food.foodId = Eat.foodId
group by food.foodTypeid
order by count(food.foodTypeid) 

Open in new window


thanks!
LVL 2
Starr DuskkASP.NET VB.NET DeveloperAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
You need to make sure that the FoodType appears regardless whether it exists in the eat table or not. Therefore it needs to be the first table in the select query and other tables need to be left joined with it:
select ft.footypeid,
ft.FoodTypeName,
coalesce(count(Eat.foodId), 0) as typecount
from FoodType ft 
left outer join food f ON ft.footypeid = f.footypeid
left outer join Eat on f.foodId = Eat.foodId
group by ft.footypeid,
ft.FoodTypeName,
order by coalesce(count(Eat.foodId), 0) desc 

Open in new window

0
 
PortletPaulfreelancerCommented:
if Food.FoodTypeId is always present (i.e. not null) then you could use an inner join at line 5 above

no points pl.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
Perfect perfect perfect!!! thanks so much!
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
thank you!  (food type is not always present, so that's why I needed it to return a 0 for meat).

PERFECT!

:)
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
oh oh, yes, PortletPaul, yes, the Inner Join is good. Then I won't get ones in FoodType that are NOT In Food. They must be in Food. thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.