Return a value if record does not exist

  • nick_roberts5

    SSC Veteran

    Points: 223

    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!

  • Tom257

    SSChampion

    Points: 11475

    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: 143327

    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

    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

    Wonderful solution! Congratulations!

  • Jeff Moden

    SSC Guru

    Points: 996623

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • David Burrows

    SSC Guru

    Points: 64733

    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.

  • This was removed by the editor as SPAM

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

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