rock.liu (4/30/2014)
can you use this one ?SELECT a.BoxId, b.field1 as value1, c.field1 as value2, d.field1 as value3, e.field1 as value4
FROM boxes a
cross apply (select top 1 b.field1 from widgets b ON a.boxid = b.boxid and b.parameter = 1 ) b
cross apply (select top 1 c.field1 from widgets c ON a.boxid = c.boxid and c.parameter = 2 ) c
cross apply (select top 1 d.field1 from widgets d ON a.boxid = d.boxid and d.parameter = 3 ) d
cross apply (select top 1 e.field1 from widgets e ON a.boxid = b.boxid and e.parameter = 4 ) e
Your code is full of errors and it does not guarantee to return the same results as the original query. You should use outer apply. And the problem remains the same, you're reading widgets table four times.