By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

Sub Query for fetching records - Urgent Please

P: 17
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
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
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.