Not able to select match and non match by left join

  • I would like to check category by lob that exists or not on the basis of @Match Parameter.

    I can do this by Exists logic by using if and else statement for @Match parameter but like to do with join

    @Match=0 Means all non match values

    @Match=1 Means all match values

    Problem : Getting same result for both @Match=0 or @Match=1 and lobid=4 :doze:

    declare @table table (categoryid int)

    declare @t table(categoryid int,lobid int)

    insert @t

    select 1,2 union all select 2,3

    insert @table

    select 1 union all select 2

    declare @match bit=0

    select a.categoryid,b.lobid from @table a left join @t b

    on a.categoryid=b.categoryid

    and (b.lobid=4 and @match=1) or (b.lobid is null and @match=0)

    Please Suggest

  • What is the expected result.

    I think this data will not return anything because there is no lobid=4 and you have match for each row.

  • The reason now that you always get all rows is because you have all conditions in the ON clause. Thus all rows in the left table are retained.

    I'm not exactly sure what you are looking for, but maybe it is this:

    select a.categoryid,b.lobid

    from @table a left join @t b on a.categoryid=b.categoryid

    and b.lobid = 4

    where (b.lobid is not null and @match=1) or (b.lobid is null and @match=0)

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • declare @table table (categoryid int)

    declare @t table(categoryid int,lobid int)

    -- added 1,4

    insert @t

    select 1,2 union all select 2,3 union all select 1,4

    -- added 3

    insert @table

    select 1 union all select 2 union all select 3

    declare @match bit=0

    select a.categoryid,b.lobid from @table a left join @t b

    on a.categoryid=b.categoryid

    where

    (b.lobid = 4 AND @match=1) or ( @match=0 AND b.lobid is null)

    set @match=1

    select a.categoryid,b.lobid from @table a left join @t b

    on a.categoryid=b.categoryid

    where

    (b.lobid = 4 AND @match=1) or ( @match=0 AND b.lobid is null)

    With these changes one gets following resulsts

    categoryidlobid

    3NULL

    and

    categoryidlobid

    14

  • Hi Erland Sommarskog,

    This is getting the same result as my query above.

    I would like to check match and non match row in one query.

    I can achieve this by below logic

    declare @table table (categoryid int)

    declare @t table(categoryid int,lobid int)

    insert @t

    select 1,2 union all select 2,3

    insert @table

    select 1 union all select 2

    declare @match bit=1

    If @match=0

    select a.categoryid,b.lobid from @table a left join @t b

    on a.categoryid=b.categoryid

    and b.lobid=4 and b.categoryid is null and @match=0

    else

    select a.categoryid,b.lobid from @table a left join @t b

    on a.categoryid=b.categoryid

    where b.lobid=4 and b.categoryid is not null and @match=1

    BUT i would like to do this in only one select statement.

    FYI - First set shows for lobid , list category id that are not associated to it

    & Second Set (ELSE PART) shows list of category id associated.

    So for second set it is precise that dataset will be blank

    but for first it will not have any category id in right table and hence its category must be blank

  • Not sure what you mean. When I run the batch you posted and my query, I do get the same results from both queries, no matter whether @match is 0 or 1.

    Then again, having two queries may be better than having a single query, as it makes code cleaner and easier to understand.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Hi Erland,

    I know that @match does not put any effect on query i just added to let developer know which part define

    define which filtration, and that is precise that if and else make code clean but i was just targeting to achieve this in one go.

    Appreciated you suggestion 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

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