sql join return null if value not found

Question

I have table with countries, resorts and photos for resorts. Each photo may be marked as title for album. I want to select joined dataset with country id and name, resort id and name and photo only if it marked as title, otherwise - null.

I tried to do following:

select
    s.id as c_id,
    s.name as c_name,
    t.id as r_id,
    t.name as r_name,
    p.thumbnail as image,
    p.description as desc,
    p.is_title
from dbo.countries s
join dbo.resorts t on t.state=s.inc
left outer join dbo.resorts_photo p on p.resort_id=t.inc
where s.inc=@country_id

And I've got list of all photos - titled and not. If I adding filering by is_title filed ( 'and p.is_title=1') all resorts which hasn't titled photo will be droppped off from result datase - not what I want.

I just want to get something like (see last row):

c_id c_name     r_id  r_name    image   desc  is_title
2    Australia  17    Adelaida  0xXXXX  NULL  1
2    Australia  178   BB Rief   0xXXXX  blah  1
2    Australia  160   Brum      NULL    NULL  NULL

Answer 1

Include the extra predicate(s) in your JOIN condition rather than the WHERE clause so that they do not cause the entire row to be removed:

LEFT OUTER JOIN dbo.resorts_photo AS p
ON p.resort_id = t.inc
AND p.is_title = 1