TSQL: How to ouput records even when where condition doesnt match

Question

I have a temp table with 3 columns "ID","Cost", "MaxCost"..below is my select statement which selects rows given particular ID..

        SELECT
            t.Cost 
            t.MaxCost
        FROM @temp t
        WHERE t.ID = @ID

How do i modify the above query so that even if given ID doesn't exists it still output rows with Cost = 0 & MaxCost = 0

score 2 · Accepted Answer · answered Dec 18 '15 at 21:14

2

Select both the actual and the default record, and select the first one ordering by their weight.

select top (1)
  Cost,
  MaxCost
from (
  SELECT
    t.Cost 
    t.MaxCost,
    1 as takeme
  FROM @temp t
  WHERE t.ID = @ID

  union all

  select 0, 0, 0
) foo
order by
  foo.takeme desc

answered Dec 18 '15 at 21:14

GSerg

76,472
17
159
346

Your answer is correct based on my question..but i think its not going to work for what i'm trying to do.. – LP13 Dec 18 '15 at 21:42
@user3862378 How is this correct when the stated question is records - plural? – paparazzo Dec 18 '15 at 22:31
@Frisbee Because plural rows does not make sense in the context of this question and hence was ignored as a typo. [Your answer](http://stackoverflow.com/a/34364467/11683) arguably makes even less sense. What the OP actually [meant to ask](http://meta.stackexchange.com/q/66377/147640) is [this](http://stackoverflow.com/q/34364484/11683). – GSerg Dec 18 '15 at 23:15
@GSerg Rows was three typos? It was a simple question. There is no purpose to attacking an answer that takes a question as written. And if it is just one ID my answer still works and is less lines than yours. – paparazzo Dec 18 '15 at 23:21

paparazzo · Answer 2 · 2015-12-18T23:25:50.407

0

declare @table table (cost int); 
insert into @table values (2), (2), (3);

declare @findCost int = 1; 

select * from @table where cost = @findCost
union all
select 0 as cost from @table where cost = @findCost having count(*) = 0;

set @findCost = 2;

select * from @table where cost = @findCost
union all
select 0 as cost from @table where cost = @findCost having count(*) = 0;

edited Dec 18 '15 at 23:25

answered Dec 18 '15 at 21:48

paparazzo

44,497
23
105
176

TSQL: How to ouput records even when where condition doesnt match

2 Answers2