Last post May 01, 2012 03:39 PM by trag1
Apr 30, 2012 04:55 PM|trag1|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).
Case if mst.col2 = 'Y'
then (this join statement)
(another join statment)
Does anyone know how to incorporate this conditional statement in? Thanks for any help
Apr 30, 2012 05:54 PM|grundebar|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.
May 01, 2012 03:39 PM|trag1|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