SQL Select Return Default Value If Null

Question

Database: MS SQL 2008

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, Pictures.Picture, Listing.ID  FROM Listing INNER JOIN Pictures ON Listing.ID = Pictures.ListingID WHERE (Pictures.ID = (SELECT MIN(ID) FROM Pictures WHERE (ListingID = Listing.ID))) 

The issue is, I have several "Listings" without a Picture, and because of this SQL script they don't show up. How can I get them to show up?

Maybe give the Pictures.Picture Column a value of "default.jpg" if the value is null? I'm pretty lost on this, so if someone could help, that'd be amazing. Sorry if I'm asking the question poorly as well, I dont understand how to ask really what I need it to do. But ask for more details and I'll post them.

Each Listing can have as many pictures as the user wants, I need this script to display a Listing even if it doesn't have a picture.

---

PHASE 2

---

Thank you all. So far I'm learning some new commands I never even knew existed. The issue now is its returning a row for each picture a listing has. But the default image is working great.

SELECT Listing.Title, Listing.MLS, coalesce(Pictures.PictureTH, '../default_th.jpg') as PictureTH, coalesce(Pictures.Picture, '../default.jpg') as Picture, Listing.ID FROM Listing LEFT OUTER JOIN Pictures ON Listing.ID = Pictures.ListingID 

How can I get it so it only returns 1 row per ListingID ?

Answer 1

Two things:

1. Use left outer join instead of inner join to get all the listings, even with missing pictures.

2. Use coalesce to apply the default

   SELECT Listing.Title     , Listing.MLS     , Pictures.PictureTH     , coalesce(Pictures.Picture, 'default.jpg') as Picture     , Listing.ID   FROM Listing  LEFT OUTER JOIN Pictures      ON Listing.ID = Pictures.ListingID  

EDIT To limit to one row:

SELECT Listing.Title     , Listing.MLS     , Pictures.PictureTH     , coalesce(Pictures.Picture, 'default.jpg') as Picture     , Listing.ID   FROM Listing  LEFT OUTER JOIN Pictures      ON Listing.ID = Pictures.ListingID  WHERE Pictures.ID is null OR Pictures.ID = (SELECT MIN(ID)      FROM Pictures      WHERE (ListingID = Listing.ID)))