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 ?

did you try the with the WHERE part of your original query ? — Gabriele Petrioli
– Gabriele Petrioli, Commented Oct 7, 2010 at 23:00
I have, and it doesn't work. I'll post the server info if people want to try some statements of their own? I'll create a new dummy database. — Landmine
– Landmine, Commented Oct 7, 2010 at 23:10

Shannon Severance · Accepted Answer · 2010-10-07 23:11:24Z

56

Two things:

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

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

edited Oct 7, 2010 at 23:11

answered Oct 7, 2010 at 22:36

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Landmine Over a year ago

But how do I make it so it only returns 1 row per listing and not as many rows as there are pictures?

Shannon Severance Over a year ago

If there is more than one Picture but you only want one row returned, what are the rules for which Picture to use?

Shannon Severance Over a year ago

Sorry, didn't see the where clause on your original query. Added that, with a test to include rows where there is no matching Picture.

Vijjendra · Accepted Answer · 2012-05-29 12:04:49Z

12

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

edited May 29, 2012 at 12:04

answered Oct 7, 2010 at 22:37

Vijjendra

25.3k15 gold badges63 silver badges93 bronze badges

Comments

Gabriele Petrioli · Accepted Answer · 2010-10-07 22:33:48Z

4

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

answered Oct 7, 2010 at 22:33

Gabriele Petrioli

197k35 gold badges275 silver badges331 bronze badges

Comments

Shannon Severance · Accepted Answer · 2010-10-07 22:33:36Z

2

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

edited Oct 7, 2010 at 22:33

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

answered Oct 7, 2010 at 22:32

ozczecho

8,9098 gold badges39 silver badges42 bronze badges

1 Comment

Landmine Over a year ago

Works, but returns a row for each picture as well. So It gives me 7 results because there are 6 pictures on listing 1 and listing 2 has no pictures, but is returned.

clyc · Accepted Answer · 2010-10-07 22:54:54Z

1

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.

answered Oct 7, 2010 at 22:54

clyc

2,45014 silver badges15 bronze badges

1 Comment

Landmine Over a year ago

MSSQL 2008, I'll post that info at the top too.

Collectives™ on Stack Overflow

SQL Select Return Default Value If Null

5 Answers 5

3 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related