内部连接到select语句，其中内部select语句的where子句引用外部select？

This is a slimmed down query of my greater problem, but the gist is that I'm trying to inner join to a select where the select is limited by the outer select. Is that possible? I'm getting an error about multipart identifier S.Item and S.SerialNum on the inner select.This is a slimmed down query of my greater prob