i am executing this query in sql server 2008 r2 the result is " Subquery returned more than 1 value. This is not permitted" Please help me -

 select purchase_id ,   (case when (select source_type dbo.tbl_purchase purchase_id  = tbl_purchase.purchase_id)=1    (select vessel_name dbo.tbl_vessel vessel_type=1)  when  (select source_type dbo.tbl_purchase purchase_id  = tbl_purchase.purchase_id)=2   (select vessel_name dbo.tbl_vessel vessel_type=2)  else  (select jetty_name dbo.tbl_jetty )   end ) vessl_name  tbl_purchase 

i think want this:

 select purchase_id ,         coalesce(v.vessel_name,j.jetty_name) vessl_name  dbo.tbl_purchase p          left join       dbo.tbl_vessel v          on             p.source_type in (1,2) ,             v.vessel_type = p.source_type          left join       dbo.tbl_jetty j          on             p.source_type not in (1,2) 

otherwise, if you're wanting continue down case/subquery path, need fix these statements:

(select source_type dbo.tbl_purchase purchase_id  = tbl_purchase.purchase_id) 

where both sides of comparison in fact referencing same table , same column, , return all rows tbl_purchase (except null purchase_id)

see query above how use aliases (p, v, j) on tables can specify correct source table each column reference. i.e. i'd expect like:

(select source_type dbo.tbl_purchase p2 p2.purchase_id  = p1.purchase_id) 

assuming original tbl_purchase in outer from clause has been given alias p1.

---

further reading:

coalesce, using joins