SQL: get avg values with null

Question

I have a problem with the following query. It isn't showing me wines that have pairings without grade (null value) and I need to show all the wines, even those without an AVG grade. How can I do that?

SELECT wineid
     , wine_name
     , winetype_name
     , wine_img
     , producer_name
     , region_name
     , sponsored
     , recipe_name
     , recipeid
     , grade_name
     , nota
  from
     ( select wine.id as wineid
            , wine_name
            , winetype_name
            , wine_img
            , producer.producer_name AS producer_name
            , region_name
            , sponsored
            , recipe_name     
            , recipe.id as recipeid
            , **round(avg(grade_id),0) as nota**
        from wine
            , producer          
            , winetype
            , region
            , recipe
            , rating
            , pairing
        where wine.id = pairing.wine_id 
         and wine.winetype_id = winetype.id 
         and wine.producer_id = producer.id 
         and wine.region_id = region.id 
         and recipe.id = pairing.recipe_id 
         and rating.pairing_id = pairing.id 
        group 
            by wineid
             , wine_name
             , recipe_name) as temp
     , grade
 where grade.id = temp.nota 
  order 
    by sponsored desc
     , nota desc;

Answer 1

Don't use implicit joins (with a comma in the from clause)! Instead, use modern, explicit joins (with the on keyword). Not only are they the de-facto standard, but also it's easy to change them to left join, which seems to be what you need here.

Here is an updated version of your query that uses the modern join syntax; I changed a few joins to left joins (you might need to adapt that more, since without seeing actual sample data it is almost impossible to guess which joins should be changed exactly):

select
    wineid, 
    wine_name, 
    winetype_name, 
    wine_img, 
    producer_name, 
    region_name, 
    sponsored, 
    recipe_name, 
    recipeid, 
    grade_name, 
    nota
from 
    (
        select 
            wine.id as wineid, 
            wine_name, 
            winetype_name, 
            wine_img, 
            producer.producer_name AS producer_name, 
            region_name, 
            sponsored, 
            recipe_name, 
            recipe.id as recipeid, 
            round(avg(grade_id), 0) as nota
            from 
            wine, 
            inner join producer on wine.producer_id = producer.id  
            inner join winetype on wine.winetype_id = winetype.id
            inner join region on wine.region_id = region.id 
            left join pairing on wine.id = pairing.wine_id 
            left join recipe on recipe.id = pairing.recipe_id 
            left join rating rating.pairing_id = pairing.id 
        group by 
            wine.id,
            wine_name, 
            winetype_name, 
            wine_img, 
            producer.producer_name, 
            region_name, 
            sponsored, 
            recipe_name, 
            recipe.id
    ) as temp 
    left join grade on grade.id = temp.nota 
order by 
    sponsored desc, 
    nota desc;

Answer 2

First thing you should know is that it is very difficult to help you without any data. We can only guess. So please do check this few things and edit your questions after you have checked them.

1.

I do not know what kind of data do you have but do check this relation:

where grade.id = temp.nota 

because the temp.nota in your code is calculated as :

round(avg(grade_id),0) as nota

and I do not believe that the grade.id the same as this calculated value. It is a bad join I believe.

2.

Please do not use JOIN like this:

as temp, grade where grade.id = temp.nota

do it like this:

as temp left join grade on grade.id = temp.nota