Return a value if record does not exist

Question

Post reply

Return a value if record does not exist

nick_roberts5

SSC Veteran

Points: 223
More actions
June 17, 2009 at 1:17 pm

#97706

Hi,
Is is it possible to retun a value from a select statement if the record does not exist in the database.
For example,
SELECT price FROM products
WHERE productname = 'bob'
How can I return 0 if there is no product called 'bob'?
Thanks!
January 10, 2020 at 1:14 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

No, the SELECT statement is designed that way.

You can achieve what you want like this (its not good practise)

IF NOT EXISTS (SELECT * FROM products

WHERE productname = 'bob')

SELECT 0 AS price;

ELSE

SELECT price FROM products

WHERE productname = 'bob';

However - how do you know if the productname 'bob' does exist, but has a price of 0? - you can't tell from the results which condition gave the result, so its not a reliable test for the existance of productname 'bob'

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

There are a couple of different ways to get around this problem. Tom lists the most straight-forward way (IF). If you need to stick to a single SQL statment or something that could be used in a view, you could use either a conditioned UNION or the following trick, an outer join of a single constant as a table source:

SELECT COALESCE(P.price, 0) as price

FROM products as P

RIGHT JOIN (

SELECT 1 as Constant

) as Dummy ON P.productname = 'bob'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

amit_adarsh Ten Centuries Points: 1273 More actions · Answer 3

Hi,

There is no way to do it in one select query directly from table .But a few work around is there

SELECT

ISNULL(P.price, 0) as price

FROM products as P

RIGHT JOIN (

SELECT

1 as productname) as PROD

ON P.productname = Prod.productname and P.productname ='bob'

make sure that 1 is not available in products table

ricoinside-sqlcentral Newbie Points: 1 More actions · Answer 4

ricoinside-sqlcentral

Newbie

Points: 1

December 18, 2019 at 6:04 pm

#3708867

Wonderful solution! Congratulations!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

ricoinside-sqlcentral wrote:

Wonderful solution! Congratulations!

Hmmm... wait for it...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 6

SELECT ISNULL((
SELECT price FROM products
WHERE productname = 'bob'
),0) AS [price]

This will only work if only one or no row is found in matching

Far away is close at hand in the images of elsewhere.
Anon.