Member Avatar for newbie26

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
  • 171 Views
  • 33 Minutes Discussion Span
  • Latest Post Latest Post by urtrivedi
Member Avatar for urtrivedi

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.