Post reply

adding condition within inner join

  • July 13, 2015 at 12:50 am

    #305690

    I am looking for a query where in I can have a conditional statement within inner join after ON statement as shown below.Please help !

    Declare @roleid int

    select @roleid = roleid from Role where Name ='Admin'

    select empid,empName,deptName from employee em

    inner department dm on CASE when @roleid>0 then 1=dm.RoleId else em.RoleId=dm.RoleId end

  • July 13, 2015 at 2:39 am

    #1811878

    CASE is an expression, it returns a value. You can use it in place of a column, not in place of a predicate

    select empid,empName,deptName from employee em

    inner department dm on CASE when @roleid>0 then 1 else em.RoleId end = dm.RoleId

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply