asked
Get the specific records
Hi, I tried to get the results with NULL and NOT NULL together. I tried and failed. So, I am asking the experts advise. DECLARE @t1 TABLE ( RowNo INT IDENTITY(1,1), Row1 VARCHAR(50), Row2 VARCHAR(50), Row3 INT, Row4 VARCHAR(50),row5 BIT ) INSERT @t1 (Row1, Row2, Row3, Row4, row5) VALUES ('1','a',1,'\1a',1),('1','b',1,NULL,0), ('2','a',1,'\2a',1),('2','b',1,NULL,0), ('3','a',1,NULL,0),('3','b',1,NULL,0) SELECT d1.Row1,d1.Row2, d1.Row3, d2.Row4 FROM @t1 AS d1 LEFT OUTER JOIN @t1 AS d2 ON d1.Row1 = d2.row1 WHERE d2.Row4 IS NOT NULL AND d1.row5 <> 1 This select returns the following results. Row1 Row2 Row3 Row4 ---- ----- ---- ---- 1 b 1 \1a 2 b 1 \2a But, the results I am expecting is Row1 Row2 Row3 Row4 ---- ---- ---- ---- 1 b 1 \1a 2 b 1 \2a 3 a 1 NULL 3 b 1 NULL Is there a way to get the results in this format?
The short answer is that when you do d2.Row4 IS NOT NULL, you are removing the NULL records. Because you want to keep the null records in, that won't work. Here's the problem: you're joining the rows on themselves. To make it easy, think of it in terms of (Row1, Row2). You have (1, a) and (1, b). When you do the LEFT OUTER JOIN, you end up with four results: - (1, a) & (1, b) - (1, b) & (1, a) - (1, a) & (1, a) - (1, b) & (1, b) In other words, you have self-joins here. It sounds like you are not expecting those self-joins, so you should filter them out. Try this query on for size: SELECT d1.Row1, d1.Row2, d1.Row3, d2.Row4 FROM @t1 AS d1 LEFT OUTER JOIN @t1 AS d2 ON d1.Row1 = d2.row1 and d2.Row2 d1.Row2 WHERE d1.row5 1 I added the "and d2.Row2 d1.Row2" there to remove self-joins. If you have an identity column, GUID, or some other unique identifier, you can replace the line I included with that to make sure you filter those records out. Once you do that, you should end up with the results you're anticipating.
Better explanation than mine. Good thing I was off wrestling with formatting.
Just move the WHERE criteria affecting the OUTER JOIN table up into the JOIN criteria and I think you'll get exactly what you want 'SELECT d1.Row1,d1.Row2, d1.Row3, d2.Row4 FROM @t1 AS d1 LEFT OUTER JOIN @t1 AS d2 ON d1.Row1 = d2.row1 AND d2.Row4 IS NOT NULL WHERE d1.row5 1'
@Sarpr if you got the answers you want, please mark one as the answer - not only does it show to future readers that the answer is right, but you will also gain more karma points, see
http://ask.sqlservercentral.com/faq/
Fine. I marked it. What good is being editor if you can't do the right thing every once in a while.
@srpr and mark @Kevin Feasel answer. It's a much better explanation.
