0

hi..can somebody help me?

how can i have a conditional join in my statement.
i have a parameter to be passed to the SP: @pRemarks

my main query:
select * from MasterList

if @pRemarks='ALL' then my query will be:
select * from MasterList inner join tblPositionLists on tblPositionLists.Position = MasterList.Position

else if @pRemarks='LEVEL' then my query will be:
select * from MasterList inner join tblPositionLists on tblPositionLists.PositionLevel = MasterList.PositionLevel

how can i achieve this through CASE WHEN? (or anything else)
.as you noticed, same table to be joined but different in column to be referenced to. But I dont want this to use IF else because the query is longer than this.

Thank You.

2
Contributors
1
Reply
13
Views
3 Years
Discussion Span
Last Post by urtrivedi
0

I am not sure, but can you give try to this

select * from MasterList inner join tblPositionLists on tblPositionLists.Position = case when @pRemarks='ALL' then MasterList.Position else tblPositionLists.Position end
and
tblPositionLists.PositionLevel = case when @pRemarks='LEVEL' then MasterList.PositionLevel else tblPositionLists.PositionLevel end

Edited by urtrivedi

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.