Sub Query for fetching records - Urgent Please

[padmaneha]

I executed the below query for getting the details of dishes which are tagged to the festival 'Durga Pooja'

select fg.tagname, fg.foodid, f.dishes, f.dishtype
from foodtag fg inner join food f
on fg.foodid = f.contentid
where tagname='Navratri / Durga Puja' and isapproved=1
order by foodid

Result is displayed as fetching some 167 records approximately

Festival tagname foodid dishes dishtype
Navratri / Durga Puja 1 Aloo Chat Punjabi Veg
Navratri / Durga Puja 30 Payasam South Indian Veg
Navratri / Durga Puja 62 Almond Kheer Veg
Navratri / Durga Puja 68 Aloo Tikki Punjabi Veg

I need to execute another query which will give the number of recipes posted for the above dishes for which I wrote a query as

select f.dishes,f.contentid,f.titleurl,count(f.dishes)as Dishescount
from food f left join foodmemberpref fmp
on f.contentid=fmp.foodid where isapproved=1 and blogcid>0
and f.contentid in(1,30,62,68)

Result is dispalyed as
Dishes contentid titleurl RecipesCnt
Aloo Chat 1 punjabi/aloo-chat 1
Payasam 30 south-indian/payasam 5
Aloo Tikki 68 punjabi/aloo-tikki 7

I need a subquery which can be written as single query to fetch records of those dishes which are tagged to Durga pooja festival and the recipes posted for these durga pooja dishes. It should also display the details of the dishes which has zero recipes. In other words, it should display all those records of 'Durga Pooja' and the corresponding recipe count displaying the dish details even though, it has got zero recipes, but tagged to durga pooja

Output should be displayed as

Festival tagname foodid dishes dishtype Recipescnt
Navratri / Durga Puja 1 Aloo Chat Punjabi Veg 1
Navratri / Durga Puja 30 Payasam South Indian Veg 5
Navratri / Durga Puja 62 Almond Kheer Veg 0
Navratri / Durga Puja 68 Aloo Tikki Punjabi Veg 7

[deepuv04]

Hi,
try the following query. If this doesnt work plz kindly post some sample data.

 
select    fg.tagname, fg.foodid, f.dishes, f.dishtype,count(f.contentid) as Dishescount
from    foodtag fg inner join 
        food f on fg.foodid = f.contentid left outer join
        foodmemberpref fmp on f.contentid=fmp.foodid          
where    tagname='Navratri / Durga Puja' and isapproved=1 and blogcid>0
group by fg.tagname, fg.foodid, f.dishes, f.dishtype
order by fg.foodid 
 

thanks