MS SQL quirk

I used sub-query as column output quite often. As shown in the following command SQL command.

select (select top 1 a from b.c=d.c) e from d

However, if you query further based on the sub-query result e, the result is not defined, as shown in the following statement.

select (select top 1 a from b.c=d.c) e, (select top 1 f from g where g.h=e) i from d

The query will run successfully but the resulting data for i is not changing based on the value e which is correct even multiple rows are returned.

The solution is quite simple. We just need to replace the e with the sub-query it represents as shown below:

select (select top 1 a from b.c=d.c) e, (select top 1 f from g where g.h in (select top 1 a from b.c=d.c)) i from d