Conditional Table Join
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Conditional Table Join

  1. #1
    Join Date
    Jul 2013
    Posts
    3

    Conditional Table Join

    Hoping someone can help me do this the cleanest way possible. I have a Stored Procedure which has some variables and a Select statement. Here is the pseudo code of what I need to do. I have written the pseudo code in the only way I can think to code this, which I know will work, but I'm wondering if there's a cleaner way to do it. Thanks!

    var pid
    if pid = 0
    select trimprobeinfo, a, b, c
    from table a join table b on (a.1 = b.1)
    join trimprobe on (b.2 = trimprobe.2)
    else
    select a, b, c
    from table a join table b on (a.1 = b.1)
    endif

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    In my opinion that is a very clean way to do it.
    A couple of comments annotating the reasons of choosing to execute one or the other query would most certainly be appreciated by the next generation of DBA reviewing the code ten years from now, at 2AM in the morning.
    Last edited by PAVB; 07-24-2013 at 10:25 AM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,480

    Cool

    What do you mean by clean way?
    These are two distinct queries returning different number of columns.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Jul 2013
    Posts
    3
    I just wasn't sure if there was a way to write only 1 query and have the additional column and join be conditional.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by KerriT View Post
    I just wasn't sure if there was a way to write only 1 query and have the additional column and join be conditional.
    You can always build a dynamic sql statement but, why make things complicated and harder to understand to future generations?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Jul 2013
    Posts
    3
    I ended up going with the 2 separate select statements embedded in the if-then-else statement. Thanks for weighing in.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width