SQL query with COALESCE and LEFT OUTER JOIN

Question

Sorry for bothering you, but I am stuck at sqlite queries :(

I have a table:

sport country place type
ski  swe 1 1
ski  nor 2 1
ski  rus 3 1
luge swe 1 1
luge usa 2 1
luge ger 3 1
bob  nor 1 1
bob  rus 2 1
bob  ger 3 1

where place is 1 for gold, 2 for silver, 3 for bronze

Now the normal displaying scenario is a list of countries, first max gold, then silver then bronze. For that example it would be:

swe g:2 s:0 b:0 sum:2
rus g:0 s:1 b:1 sum:2
usa g:0 s:1 b:0 sum:1
nor g:0 s:0 b:2 sum:2

to do that I am doing:

select
    country,
    sum(case when place = 1 then 1 else 0 end) as gold,
    sum(case when place = 2 then 1 else 0 end) as silver,
    sum(case when place = 3 then 1 else 0 end) as bronce,
    count(*) as allmedals
from 
    results 
group by 
    country 
order by 
    gold DESC, silver DESC, bronce DESC

That works so far. But I have another table "countries", full name, short name and some "type".

[name] [short]
usa      USA  
germany  GER  
russia   RUS  
norway   NOR  
poland   POL  

I want to combine tables "results" and "countries", so that I have a list all of them from the table "countries", and if there is no results there, then its just a 0. so I tried:

SELECT 
    c.name as c_name,
    COALESCE(sum(case when r.place = 1 then 1 else 0 end), 0) as gold,
    COALESCE(sum(case when r.place = 2 then 1 else 0 end), 0) as silver,
    COALESCE(sum(case when r.place = 3 then 1 else 0 end), 0) as bronce
FROM 
    countries AS c 
LEFT OUTER JOIN 
    results AS r ON c.short = r.country 
WHERE 
    r.type = 1 
GROUP BY 
    r.country
ORDER BY 
    gold DESC, silver DESC, bronce DESC, c.name DESC" 

But the problem is, I see only a list of countries, that have a row in the table "results". I think its because of "r.type = 1"

Answer 1

Try changing

LEFT OUTER JOIN results as r ON c.short = r.country 
WHERE r.type = 1

to

LEFT OUTER JOIN results as r ON c.short = r.country and r.type = 1

This works for me just fine

select 
c.name,
sum(case when place = 1 then 1 else 0 end) as gold,
sum(case when place = 2 then 1 else 0 end) as silver,
sum(case when place = 3 then 1 else 0 end) as bronce,
count(*) as allmedals
from countries  c 
left outer join results r on c.short = r.country
group by c.name
order by gold desc, silver desc, broncedesc, c.name desc