When I first looked at this thread it just seemed relatively easy, so rather than look at all the other posts, I just started coding, and here's what I came...

John,

That doesn't produce the desired results.

faylc,

I don't see any description of what those desired results represent, in any portion of your post.   I'm not...

For all the talk about C# being so superior, I have to question the judgement of anyone that perceives this to be so.   Let's take a closer look at some...

I'm not sure if this is right, but I've added a condition to the JOIN to the order line table.   I also cleaned up the formatting and used IFCODE tags...

First, your CASE statement isn't going to run at all as a query against any version of SQL Server.   CASE statements don't support having the IF statement embedded within.    You...

Just curious, but within VS 2015, can you set the version of SQL Server that a given project applies to?    Just thinking about the possibility that the syntax checker might...

Your query directly contradicts your desired results.   At least one record exists that prevents that 2nd record from showing up, so your conditions for your query are not what they...

Please note that I've posted two corrections to the query above.