如何在不使用连接的情况下重写查询

I would like to know how this query would be written if no joins were used. I been trying to figure it out for cases where joins aren't viable or can't be used(aren't available).I would like to know how this query would be wr