Sql join return null if value not found

Question

I have a table with countries, resorts and photos for resorts. Each photo can be marked as an album name. I want to select a combined data set with a country identifier and a name, resort identifier, name and photo, only if it is marked as a header, otherwise null.

I tried to do the 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 have a list of all the photos - under the name and not. If I add filing by is_title filed ('and p.is_title = 1'), all resorts that do not have a named photo will be dropped from the original database - not what I want.

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

 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 an additional predicate in your JOIN condition, rather than a WHERE clause, so that they do not cause the entire row to be deleted:

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