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 dont 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

Need to do a LEFT join

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, Pictures.Picture, Listing.ID 
FROM Listing LEFT JOIN Pictures ON Listing.ID = Pictures.ListingID

Answer 2

Use left outer join instead of inner join

Inner join will return results if and only if there is a result that satisfies the join.

Left outer join will return results from the left side table, and if the join is satisfied also add results from the right side table..

If you need to convert the null values returned from the non-satisfying joins, then use coalesce function like coalesce(Pictures.Picture, 'default.jpg')

Answer 3

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))) 

Answer 4

Hi, if you want to set the default value if the Pic is null you can do this via COALESCE key word:

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, 
COALESCE (Pictures.Picture, '"default.jpg') AS Pictures, Listing.ID 
FROM Listing LEFT JOIN Pictures 
ON Listing.ID = Pictures.ListingID
WHERE (Pictures.ID = (SELECT MIN(ID) 
FROM Pictures WHERE (ListingID = Listing.ID)))

You can also achieve this via IsNull like below:

SELECT Listing.Title, Listing.MLS, Pictures.PictureTH, 
ISNULL(Pictures.Picture, '"default.jpg') AS Pictures, Listing.ID 
FROM Listing LEFT JOIN Pictures 
ON Listing.ID = Pictures.ListingID
WHERE (Pictures.ID = (SELECT MIN(ID) 
FROM Pictures WHERE (ListingID = Listing.ID)))

you can read here about IsNull and Coalesce

Answer 5

Landmine, what database are you using?

If it's sql server 2005 or above or oracle, you can use the pivot command to achieve this.