SQL Subquery within a subquery [closed]

I am writing a nested SQL where I need to have a subquery within a subquery

Let’s say I am trying to retrieve a row from ORDERS that matches MAIN_ID from SOURCE that matches MODULE from MODULES.

Select MAIN_ID from ORDERS
where MAIN_ID in (select MAIN_ID
                  from SOURCE
                  where MODULE in (select MODULE
                                   from MODULES
                                   where MODULE2 = 'zzzz'))

This results in a bunch of rows returning 0’s. Not sure how I can get around it. Only thing I know is MODULE2 in this case which is ‘zzzz’. I have to be able to reverse back to find the row from ORDERS table.

Any help would be great.

Answer

Please try to write the query as a join. Maybe you can see why zeros are returned

Select O.*, S.*, M.* 
from ORDERS O 
JOIN SOURCE S ON S.MAIN_ID = O.MAIN_ID
JOIN MODULES M ON S.MODULE = M.MODULE
WHERE M.MODULE2 = 'zzzz'