CODEWITHSUNDEEP
sqlsql-serveroracle-databaseoracle11g
MAX
MAX

Reputation: 1550

Subquery in Case Expressions

I have the following data.

Output:

id  cid price   sold
--------------------
 1  1   10      20     
 2  1   30      40      
 3  2   50      60

What I need is this: 

 id cid Amount
--------------
  1 1   20     
  2 1   10      
  3 2   30

If id = cid then sold as amount else

If id != cid then for cid's matching id's price as amount ( Please refer above output, if it is confusing! )

I have the following SQL query:

 declare @tg table(id int, cid int, price int, sold int)

 insert into @tg values(1, 1, 10, 20); 
 insert into @tg values(2, 1, 30, 40);
 insert into @tg values(3, 2, 50, 60);

SELECT 
    id, cid, 
    CASE WHEN id  = cid 
           THEN sold 
         WHEN B.id IN (SELECT 
                           CASE WHEN id != cid 
                                THEN cid 
                           END AS cid1 
                       FROM @tg A 
                       WHERE A.cid = B.id) 
           THEN price  
    END as amount 
FROM 
    @tg AS B
 --OR
select
id,cid,
CASE WHEN id  = cid THEN sold else price END as amount from @tg
 as B

it's not working when id != cid.

Upvotes: 0

Views: 113

Answers (4)

G.Nagababu
G.Nagababu

Reputation: 43

select 
  id, 
  cid, 
  case when cid = id then sold 
    else (select price from @tg where id=c.cid )
  end as amount  
from @tg c

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269583

I think this is clearer just as a join:

select tg.*, tgc.price as sold
from @tg tg left join
     @tg tgc
     on tg.cid = tgc.id;

You are just looking up the value in the same table. You don't actually need to short-circuit the join.

Or, if you prefer a subquery:

select tg.*,
       (select tgc.amount from @tg tgc where tgc.id = tg.cid)
 from @tg tg;

In both these cases, it is important to use table aliases. Otherwise, you cannot distinguish between the temporary tables.

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Modify SELECT like this 

select  id,cid, CASE WHEN id  = cid THEN sold 
  WHEN id  <>  cid 
      THEN price  
      END as amount from @tg
 as B

this will return sold when id = cid other wise Prize

Upvotes: 1

sagi
sagi

Reputation: 40481

select id,cid,case when id=cid then sold else (select price from @tg t1 where t1.id = t2.cid) end as amount
from @tg t2

I think thats what you are looking for, IF id=cid take sold, else > take the corresponding price from the sold table where cid = id

Upvotes: 1

Related Questions