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.
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'