Join

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  May 18, 2015 at 9:41 am

  #308310

  Is it possible to join based on conditions

  I am trying to join Project_Code with Manager.Code but if the project_code is blank then it should look for Project_CostCenter and Join with ManagerCode .

  Project_Code = Manager.Code ( But if Project_Code is blank)

  Then Project_Cost_Center = Manager.code

  I tried COALESCE but somehow my results don't show as expected, also the Projectcode is blank and not Null

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  May 18, 2015 at 9:57 am

  #1798686

  sharonsql2013 (5/18/2015)

  ---

  Is it possible to join based on conditions

  I am trying to join Project_Code with Manager.Code but if the project_code is blank then it should look for Project_CostCenter and Join with ManagerCode .

  Project_Code = Manager.Code ( But if Project_Code is blank)

  Then Project_Cost_Center = Manager.code

  I tried COALESCE but somehow my results don't show as expected, also the Projectcode is blank and not Null

  You could try something like:

  Manager.Code = (CASE WHEN Project_Code != '' THEN Project_Code ELSE Project_Cost_Center END)

  Chances are performance won't be great, but it should provide the condition you're looking for.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• sharonsql2013

  SSCertifiable

  Points: 5908

  More actions

  May 18, 2015 at 10:21 am

  #1798702

  Yes, I will definitely try this.

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 19, 2015 at 5:05 am

  #1798959

  Or something like

  ON

  (Project_Code = Manager.Code AND Project_Code <> '')

  OR

  (Project_Code = '' AND Project_cost_center = Manager.Code)

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  May 19, 2015 at 6:01 pm

  #1799204

  sharonsql2013 (5/18/2015)

  ---

  Is it possible to join based on conditions

  I am trying to join Project_Code with Manager.Code but if the project_code is blank then it should look for Project_CostCenter and Join with ManagerCode .

  Project_Code = Manager.Code ( But if Project_Code is blank)

  Then Project_Cost_Center = Manager.code

  I tried COALESCE but somehow my results don't show as expected, also the Projectcode is blank and not Null

  Please post your code so we can try to figure something else out because conditional joins typical result in some pretty slow performance and pretty high resource usage.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)