Sql case statement or another approach? RSS

• • Reply
  

  trag1

  Member

  20 Points

  61 Posts

  Sql case statement or another approach?

  Apr 30, 2012 04:55 PM|LINK

  I'm severly rusty in my sql coding and finding something I once thought easy..difficult.

  I'm trying to use a case statement to execute one of two choices with no luck and not sure if it's the right approach or another way is better.

  Below is my basic query running a join, but I have a need now to look to see if a column is flagged Y or N and return the row values for the Y condition and not the N condition.

  Select mst.Col1, mst.Col2, ust.Col3
  From mySuperTbl mst
  JOIN unSuperTbl ust on mst.Col1 = mst.Col1
  WHERE ust.col1 = mst.Col1
  AND mst.Col1 = 123

  So say running this query produces two rows, I'd like to return only the row that contains the 'Y' value for say mst.Col2 and if no Y value is present simply return the row with N (this would be the only row available should Y not be present as it never gets inserted into the table anyways).

  Select ...

  Case if mst.col2 = 'Y'

  then (this join statement)

  else

  (another join statment)

  Does anyone know how to incorporate this conditional statement in?  Thanks for any help 

• • Reply
  

  grundebar

  Contributor

  4515 Points

  726 Posts

  Re: Sql case statement or another approach?

  Apr 30, 2012 05:54 PM|LINK

  You can't do conditional joins but you could do something like this pseudo-sql:

  Select COALESCE(u1.col2, u2.col2) as YorN, *
  From supertable t1
      Left Outer Join unsupertable u1 On t1.col1 = u1.col1
      And u1.col2 = 'Y'
      Left Outer Join unsupertable u2 On t1.col1 = u2.col1
      And u2.col2 = 'N'
  WHERE blah blah blah

  This would get your supertable results with all y or no results, then pull in a set precedence order Y, then N. The key here is to get your joins right so that you have both Y and N results on one row. Then the Coalesce function will allow you to pull the first non-null value from a list of column values.

  "What I hear, I forget; What I see, I remember; What I do, I understand." --Confucius
  
  Remeber to Mark as Answer if this post helped you.
• • Reply
  

  trag1

  Member

  20 Points

  61 Posts

  Re: Sql case statement or another approach?

  May 01, 2012 03:39 PM|LINK

  Thanks for the quick reply grundebar.  I played around with that and could not get it working properly.  I could either get both rows back or it seemed that the first Join worked and the second never did or I got no results.  Anyways, since my original issue of having to select between two rows if a say Col2 contained a Y (meaning two rows are present one for Y and one for N as there will always be at least 1 row with a value of N) I went a much simpler route of just doing a TOP 1 and an Order By on Col2 which will give me just the Y value should two rows exist and the N value if 1 row exists.

  Thanks again for your suggestion and time

  • Marked as answer by Ming Xu - MSFT on May 31, 2012 12:39 PM