September 1, 2013 at 1:48 am
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
September 1, 2013 at 3:18 am
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.
September 1, 2013 at 4:46 am
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]
September 1, 2013 at 5:38 am
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
September 2, 2013 at 3:54 am
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
September 2, 2013 at 4:53 am
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]
September 2, 2013 at 11:00 pm
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