March 24, 2011 at 3:25 pm
I have a stored procedure as such
DECLARE @Flagged bit
SELECT a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
IF @Flagged = 0 THEN
LEFT OUTER JOIN [dbo].[FlaggedRecords] fr
ON uf.[UserID] = a.[UserID]
ELSE IF @FlaggedID = 1 THEN
INNER JOIN [dbo].[FlaggedRecords] fr
ON uf.[UserID] = a.[UserID]
As you can see I would like to LEFT JOIN if the parameter has one value and INNER JOIN if it has another. Is there a way to dynamically set the JOIN condition within a single statement?
March 24, 2011 at 4:09 pm
No you can't break a single statement up with IFs.
You can do something like this, but I'm not sure it is optimal:
DECLARE @Flagged bit
SELECT
a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
LEFT OUTER JOIN [dbo].[FlaggedRecords] fr
ON fr.[UserID] = a.[UserID]
WHERE
@Flagged = 0
OR fr.[UserID] IS NOT NULL;
By doing that you get the result you want. However you might get much better performance this way:
DECLARE @Flagged bit
IF @Flagged = 0
SELECT
a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
LEFT OUTER JOIN [dbo].[FlaggedRecords] fr
ON fr.[UserID] = a.[UserID]
ELSE
SELECT
a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
INNER JOIN [dbo].[FlaggedRecords] fr
ON fr.[UserID] = a.[UserID];
March 24, 2011 at 4:11 pm
As a note there are a couple problems in your sample query:
* The aliases don't match "fr" vs. "uf"
* You have the logic backwards from what you title says "LEFT OUTER JOIN if True INNER JOIN if False". (False=0)
March 24, 2011 at 7:39 pm
CELKO (3/24/2011)
<snip>SELECT A.column_a, A.column_b
FROM TableA AS A
LEFT OUTER JOIN
Flagged_Records AS FR
ON UF.user_id = A.user_id
WHERE CASE WHEN @in_stupid_flg = 'INNER'
AND FR.user_id IS NULL
THEN 'T' ELSE 'F' END = 'T';
You might also look at EXCEPT and INTERSECT.
Except when @in_CelkosAnnoying_Flag <> 'INNER' (ie: The other option for the flag), nothing returns because it all evaluates to 'F'. So, this wouldn't be an accurate way, regardless of it being a good way.
If you're going to be arrogant, dismissive, and insulting, at least get it right.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 24, 2011 at 7:56 pm
Craig Farrell (3/24/2011)
CELKO (3/24/2011)
<snip>SELECT A.column_a, A.column_b
FROM TableA AS A
LEFT OUTER JOIN
Flagged_Records AS FR
ON UF.user_id = A.user_id
WHERE CASE WHEN @in_stupid_flg = 'INNER'
AND FR.user_id IS NULL
THEN 'T' ELSE 'F' END = 'T';
You might also look at EXCEPT and INTERSECT.
Except when @in_CelkosAnnoying_Flag <> 'INNER' (ie: The other option for the flag), nothing returns because it all evaluates to 'F'. So, this wouldn't be an accurate way, regardless of it being a good way.
If you're going to be arrogant, dismissive, and insulting, at least get it right.
SSC needs a "Like" button 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMarch 24, 2011 at 11:56 pm
Hi,
I believe that htis will solve your problem:
select a.UserId,a.columnA,a.ColumnB from tableA a
left outer join FlaggedRecords b
on a.USerId=b.USerId
where ((@Flag=0 and b.UserId is null) or (@flag=0 and b.USerId is not null))
Or
(@Flag=1 and b.UserId is not null)
When @Flag=0 it covers both cases - when records exist in FlaggedRecords table and when they don't exist there (so it behaves as LEFT)
When @Flag=1 it will behave as INNER JOIN
Hope it helps!
MB
March 24, 2011 at 11:59 pm
LOL
Just realized, my previous post is same as:
select a.UserId,a.columnA,a.ColumnB from tableA a
left outer join FlaggedRecords b
on a.USerId=b.USerId
where ((@Flag=0 ))
Or
(@Flag=1 and b.UserId is not null)
Even easier!
March 25, 2011 at 2:41 am
Craig Farrell (3/24/2011)
If you're going to be arrogant, dismissive, and insulting, at least get it right.
Almost every post I have seen from Celko has been arrogant, dismissive and insulting.... what is his problem?
He may know his stuff, but because of his attitude, I wouldn't open the cover of any of his books, let alone buy one.
March 25, 2011 at 7:21 am
Hi guys,
My apologies, just realized that I pretty much repeated code already posted by someone else (never do things in a hurry!).
MB
March 28, 2011 at 6:26 am
Ian Scarlett (3/25/2011)
Craig Farrell (3/24/2011)
If you're going to be arrogant, dismissive, and insulting, at least get it right.Almost every post I have seen from Celko has been arrogant, dismissive and insulting.... what is his problem?
He may know his stuff, but because of his attitude, I wouldn't open the cover of any of his books, let alone buy one.
Same feeling here...
Attitude and knowledge are 2 distinctive factors.
No matter how much knowledge a person may have, if he's attitude sucks he won't be getting any respect from me.
March 28, 2011 at 9:35 am
Always Learning (3/24/2011)
I have a stored procedure as suchDECLARE @Flagged bit
SELECT a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
IF @Flagged = 0 THEN
LEFT OUTER JOIN [dbo].[FlaggedRecords] fr
ON uf.[UserID] = a.[UserID]
ELSE IF @FlaggedID = 1 THEN
INNER JOIN [dbo].[FlaggedRecords] fr
ON uf.[UserID] = a.[UserID]
As you can see I would like to LEFT JOIN if the parameter has one value and INNER JOIN if it has another. Is there a way to dynamically set the JOIN condition within a single statement?
There's a great article on using dynamic sql to do things like this here.
http://www.sommarskog.se/dyn-search-2005.html
Basically you could do this one of two ways.
DECLARE @cmd nvarchar(max)
SELECT @cmd = N'
SELECT a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
'
IF @Flagged = 0 THEN
SET @cmd = @cmd + N'
LEFT OUTER JOIN [dbo].[FlaggedRecords] fr
ON uf.[UserID] = a.[UserID]'
ELSE
SET @cmd = @CMD + N'
INNER JOIN [dbo].[FlaggedRecords] fr
ON uf.[UserID] = a.[UserID]'
EXEC sp_executesql @cmd
The other way would be to always LEFT JOIN and then test for something like
SELECT a.[ColumnA]
, a.[ColumnB]
FROM [dbo].[TableA] a
LEFT OUTER JOIN [dbo].[FlaggedRecords] fr
ON fr.[UserID] = a.[UserID]
WHERE
(@Flagged = 1 AND fr.[UserID] IS NOT NULL) OR
(@Flagged = 0)
You'll find that as soon as your queries start to get complicated the dynamic approach above will result in better query plans.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply