Left Outer Join

Left Outer Join

• Addy

  What it sounds like you want is something like a PIVOT table. A PIVOT table takes a set or rows and converts them into columns (hence PIVOT). You can do this in SQL 2000 by using a SUM and a CASE statement with a GROUP BY, as illustrated below:

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/acdata/ac_8_qd_14_04j7.asp

  However, instead of pivoting your data, you want to select one value (if there are multiple) depending on the values you have. You can use the same logic as with the PIVOT table for this, only you would just apply this to one column, not several, and use a MAX or MIN not a SUM to get the first or last value alphabetically, depending on where your preferred value lies in the alphabetical order. To push you value to the front alphabetically, you can prepend a space (although you won't need to do this if the preferred value is first or last alphabetically). You code will look something like this:

  SELECT Number, LTRIM(MAX(sub.desc))
  FROM
  ( SELECT Support_Problem.Number,
  CASE WHEN Breach.Keyword = 'Desc1' THEN ' ' + Breach.Keyword
  ELSE NULL
  END
  FROM Support_Problem Support_Problem
  Left Outer Join Support_Breach Support_Breach
  ON Support_Problem.Number=Support_Breach.Number
  ) AS sub

  You can expand the CASE statement as desired to handle your specific CASES.

  HTH. For more SQL tips, check out my blog below:

  
  

• cristof

  Hi Shughes

  Your conclusion is spot on. A one to one join would have worked beautifully if there were always records in the Breach Table.

  I'm connecting to the SQL DB from Excel using an ADODB object and an SQLOLEDB provider. The query is compiled in VBA (Macro) and imported from a record set as this is the quickest. I do not want to import the data using a loop with an EOF as this will be alot slower and would be obvious when importing 50 000 records.

  Is there a way of building the Case into the SQL query or any work around Please provide an example as I'm quite a novice with SQL.

  
  

• ZeR_o

  Hi,

  Just a quick question, How would you defined which Breach.Keyword to show when there is multiple values for one Problem.Number

  Once you answer the question you might be able to work with something like this :

  SELECT Support_Problem.Number,Branch.Keyword

  FROM Support_Problem Support_Problem

  LEFT OUTER JOIN (SELECT Breach.Number,Breach.Keyword,<<Answer to my question goes here>> FROM Support_Breach) Breach ON Support_Problem.Number = Breach.Number

  HTH,

  Eric

  
  

• K-Mile

  Hi Mark

  This works beautifully! Thank you for your support!

  
  

• yannickm

  
  If you don't mind which Breach.Keyword you want,
  you can do this

  
  Select
  Support_Problem.Number, MAX(Breach.Keyword)
  From
  Support_Problem Support_Problem
  Left Join Support_Breach Support_Breach ON Support_Problem.Number=Support_Breach.Number
  Group by Support_Problem.Number

  
  

• Anand Raman - MSFT

  Hi Aiwa

  The Breach.Keyword is not defined and could be any value. The duplication in the Breach DB is due to bad Administration and this will be highligted to the DBA's but for now I need to retrieve any of these values to get some indication of the breach reason if any.

  
  

• Hosam Kamel

  Hi Shughes

  Thanks for the Case example! I will definately use this in other queries. Mark helped me out with a simple MAX that works great but thanks again for your support.

  
  

• CurtGibson

  You have a one to many relationship, so join to the breach table will return multiple rows in the problem table if there are multiple rows in the breach table with the same Number. The only way to resolve this is if you want to choose one breach.keyword over another (eg use max or min on the 'keyword' to choose the last or first alphabetically). There are more complex and intelligent ways to chose one over another, such as using a case statement to select one value (using a more complex ccondition), or null (if anything else(using the CASE WHEN ....ELSE...END syntax)), and then combine the results from the CASE with a MAX to remove the NULL values. However, it seems like you may need to restructure you database or your query, as it does not give you the information you want currently. HTH
  

• cthistle

  Can you give us a sample table structure, some data (via insert statements) and the desired results. Makes it a lot easier to build an example query without guesses.
  
  
• Left Outer Join