LEFT OUTER JOIN if True INNER JOIN if False

  • 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?

  • 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];

  • 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)

  • 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.


    - Craig Farrell

    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

  • 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. Selburg
  • 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

  • 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!

  • 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.

  • Hi guys,

    My apologies, just realized that I pretty much repeated code already posted by someone else (never do things in a hurry!).

    MB

  • 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.

  • Always Learning (3/24/2011)


    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?

    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.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

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