CODEWITHSUNDEEP
sqlsql-serverjoinwhere-clause
Mahesh
Mahesh

Reputation: 8892

Left Join and conditional where not giving desired result?

I dont know how to freame this question. But read the description. I have two tables as below,

EntityProgress table- 

 EntityID       CurrentStateID
   101             1154
   201             1155
   301             1155

EnityApprovar Table

 EntityID        StateID        ActorID
   201             1154            8
   201             1154            9
   201             1155            8
   301             1154            8
   301             1154            9
   301             1155            9

Now What I want is if I pass the ActorID=2 as parameter then it should return only one row as below, Because we dont have any matching enityID in the entityapprovar table. 

   EntityID      CurrentStateID     
    101              1154

But If I pass the ActorID=9 then it should give me the result as below,

   EntityID      CurrentStateID
      301            1155

Because we have the entityID matching record in the EntityApprover table and also for that entityID we have the currentstateID and for that we have the actorid as 9.

So to get the result I have done as below,

 SELECT
      E.EntityID,
      E.CurrentStateID
 FROM 
      EntityProgress E LEFT JOIN EntityApprover EP 
      ON E.EntityID = EP.EntityID AND E.CurrentStateID = EP.StateID
 WHERE
     -- some conditions
     AND ((ISNULL(EP.ActorID,0) )= 0 
           OR ((ISNULL(EP.ActorID,0))!= 0 AND EP.ActorID = @ActorID AND Ep.CurrentStateID = E.StateID))

BUt When I pass the 2 I get the first result but when I pass the 9/8 I dont get the desired result. May be this is simple but I am stuck with it. I need some one others view to give me different way. IN case of the confusing feel free to leave the comment.

Upvotes: 3

Views: 90

Answers (3)

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Here is my attempt to answer you.

Query

DECLARE @ActorID int = 2

DECLARE @EntityProgress table
(
    EntityID int,
    CurrentStateID int
)

DECLARE @EnityApprovar table
(
    EntityID int,
    StateID int,
    ActorID int
)

INSERT into @EntityProgress
    values (101, 1154),
            (201, 1155),
            (301, 1155)


INSERT into @EnityApprovar
    VALUES (201, 1154, 8),
            (201, 1154, 9),
            (201, 1155, 8),
            (301, 1154, 8),
            (301, 1154, 9),
            (301, 1155, 9)

SELECT
    E.EntityID
    ,E.CurrentStateID
    ,EP.ActorID
FROM @EntityProgress E
LEFT JOIN @EnityApprovar EP
    ON E.EntityID = EP.EntityID
    AND E.CurrentStateID = EP.StateID
WHERE ((EP.ActorID IS NULL AND NOT EXISTS (SELECT 1 FROM @EnityApprovar WHERE ActorID = @ActorID))
        OR (EP.ActorID = @ActorID))

When you pass @ActorID = 2 then it'll give below output.

EntityID    CurrentStateID  
101          1154

And when you pass @ActorID = 9 then it'll give below output.

EntityID    CurrentStateID
301         1155

Which is as expected you want.

Upvotes: 1

Julius
Julius

Reputation: 1

Your lookup table for ActorId should be your query's starting point. If there's none in your schema (which I doubt), you can try this as a starting point. Using T-SQL syntax:

    DECLARE @ActorID int
    SET @ActorID = 2

    SELECT * FROM
         (SELECT @ActorID AS EntityId) 
    Actor
    LEFT JOIN
    EntityApprover EA
    ON Actor.EntityId = EA.EntityId ...

And from there you can extract the other column values.

Upvotes: -1

Sean
Sean

Reputation: 1474

You'd have to include a not exists in the query as the row that you are trying to exclude when passing in 9 has no information to determine that there are other rows in the table that match.

i.e.

SELECT
      E.EntityID,
      E.CurrentStateID
 FROM 
      EntityProgress E LEFT JOIN EnityApprovar EP 
      ON E.EntityID = EP.EntityID AND E.CurrentStateID = EP.StateID
 WHERE ((ISNULL(EP.ActorID,0) = 0 
   and   not exists(select 1 
                      from EnityApprovar ep2 
                     where ep2.ActorID = @ActorID ))
    OR  (ISNULL(EP.ActorID,0) != 0 
   AND   EP.ActorID = @ActorID  
   AND   E.CurrentStateID = Ep.StateID))

Upvotes: 1

Related Questions