467,908 Members | 1,851 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,908 developers. It's quick & easy.

Sub Query for fetching records - Urgent Please

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
Sep 23 '08 #1
  • viewed: 1408
Share:
1 Reply
deepuv04
Expert 100+
Hi,
try the following query. If this doesnt work plz kindly post some sample data.

Expand|Select|Wrap|Line Numbers
  1.  
  2. select    fg.tagname, fg.foodid, f.dishes, f.dishtype,count(f.contentid) as Dishescount
  3. from    foodtag fg inner join 
  4.         food f on fg.foodid = f.contentid left outer join
  5.         foodmemberpref fmp on f.contentid=fmp.foodid          
  6. where    tagname='Navratri / Durga Puja' and isapproved=1 and blogcid>0
  7. group by fg.tagname, fg.foodid, f.dishes, f.dishtype
  8. order by fg.foodid 
  9.  
thanks
Sep 23 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by bhargav.desai | last post: by
reply views Thread by Shujun Huang | last post: by
2 posts views Thread by Muddasir | last post: by
2 posts views Thread by =?Utf-8?B?QVZM?= | last post: by
2 posts views Thread by Bill McCormick | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.