Distinct Values from a Left Outer Join

[Dross]

Using SQL Server 2008

I have a pretty long query but the problem I am having is on the left join. I need to left join because there may or may not be a value in there, but when the record has a value I get both records (a value and then a null) and when there is no value I get the single Null value. What I am trying to do is if there is a value there only return that single value and if there is no value then return the Null. I will show examples below.

JOIN Example
LEFT OUTER JOIN TABLE1 1 on 1.ID = 2.ID AND 1.Number = '1234'

Results
John Smith 1234
John Smith NULL
Jane Doe NULL

Wanted Results
John Smith 1234
Jane Doe NULL

Any help would be appreciated

 

[lameid]

Assuming your relationship is 1 to 1....

Group By all your columns not on the outside of the outer join and then take the max of number (this will work with text too).

Otherwise if it is one to many or you want to try something more involved in case it has better performance;
Make a temp table, insert the inner join to it. Next you want the append the unmatched values now that you have the matches so Insert into the temp table based on the original query WHERE the join column on the outside is null. Lastly, select from the temp table for your results.