sql server 2008 case statement in where clause not working -

i have 3 table, documents, sr , events

in documents saving documents related sr , events.

and want show documents in 1 page.

so using select query

select *  documents d, sr s, events e  d.relationid = ( case d.documenttype when 'sr' s.srid else 'e.eventid end)  

but it's not working.

my document table structure :

documentid int,  documenttype nvarchar(50), relationid int, document image, docdate date 

can please point me mistake ?

i want select documents related info. means if sr document sr details should display otherwise events. there 2 types of documents right now.

what should select query ?

you can join using left join,

select  d.*,         coalesce(s.col1, e.col1) col1,         coalesce(s.col2, e.col2) col2,         coalesce(s.col3, e.col3) col3,         coalesce(s.col4, e.col4) col4    documents d         left join sr s             on d.relationid = d.srid         left join events e             on d.relationid = e.eventid 

where col1,...., col4 columns of each table youw ant displayed based on documenttype.

to further gain more knowledge joins, kindly visit link below:

• visual representation of sql joins

the more safe version of query above assuming same id can contain on sr , events table using case()

select  d.*,         case when d.documenttype = 'sr' s.col1 else e.col1 end) col1,         case when d.documenttype = 'sr' s.col2 else e.col2 end) col2,         case when d.documenttype = 'sr' s.col3 else e.col3 end) col3,         case when d.documenttype = 'sr' s.col4 else e.col4 end) col4    documents d         left join sr s             on d.relationid = d.srid         left join events e             on d.relationid = e.eventid