avg() value to a default value 0 for non existent records in foreign table

Tables

• SubscriptionMaster (subId,subLink)
• AlertMaster (alertId,alertTitle,alertText,subId)
• AlertDetails (alertId,userId,voteVal,userNote)
  • alertId is (F.K.) to AlertMaster

Query:

  SELECT alrt.alertid,
         alrt.alertTitle, 
         alrt.alertDetails, 
         alrt.开发者_JS百科alertDate, 
         alrt.alertURL,
         sub.subTitle,
         avg(dtl.avgVote) 
    FROM alertDetails dtl,
         ALERTMASTER alrt,
         SUBSCRIPTIONMASTER sub
   WHERE alrt.subId = 1 
     AND alrt.subId = sub.subId 
GROUP BY alertId 
ORDER BY alertDate DESC 
   limit 0,10;

There are many records in alertMaster for which there wudnt b any vote in alertDetails. When I execute above query i get -0.2500 for those records. Any ways to get 0 for such records there?

What you need is a Left Join from AlertMaster to AlertDetails. However, your original query did not include any condition by which AlertDetails are related to the other two tables nor do we know from which table most of the columns are found because they do not include a table alias. Also, you should get out of the habit of using the comma-delimited syntax and should instead use the ISO Join syntax.

Lastly, you are trying to order on AlertDate but it is not included in the Group By clause. This is a "feature" of MySQL that IMO should be abandoned. The SQL specification requires that all columns in the Select clause exist in the Group By clause or a column be contained in an aggregate function. So, either group on alertDate or wrap it and the others in an aggregate function.

Select A.alertid
    , Min( A.alertTitle ) As alertTitle
    , Min( A.alertDetails ) As alertDetails
    , Min( A.alertDate ) As alertDate
    , Min( A.alertURL ) As alertUrl
    , Min( S.subTitle ) As subTitle
    , Coalesce( Avg( D.avgVote ), 0 ) As avgVote
From AlertMaster As A
    Join SubscriptionMaster As S
        On S.subid = A.subId
    Left Join AlertDetails As D
        On D.alertId = A.alertId
Where A.subId=1 
Group By A.alertId
Order By alertDate DESC 
Limit 0,10;