I am not sure if this is possible, but I need help with a nested query. I have a transactional table and all transactions happened within this table. I want to join the nested table with the main table and display the "Order" field. I can use the "Part" to link, but it is return more records and not give me the correct value. So next is compared it by the closest date.
Example, the main query if the date is 2/1/2011 look at the nested result and if anything that is equal or closest date and the "Main Component" match then use that one . So in the screenshot, the 2nd diagram the next closest date is 7/29/2011 and "Main Component" is 7788-0111. Then it's order 9468. Next one is 7/15/2013 from main query. Look at nested query and next closest date is 10/29/2013 and "Main Component" is 998832-110, then use that one. Make sense?
So here's my query:
SELECT T.TRANS_ID TRX, T.TRANS_DATE TRX_DATE, T.PART SUB_COMPONENT, T.TASK JOB, W.PART MAIN_COMPONENT, X.ORDER
FROM TRANS_DETAIL T LEFT OUTER JOIN WORKORDER W ON T.TASK = W.TASK
LEFT OUTER JOIN
SELECT T.TRANS_DATE TRX_DATE, T.ORDER, T.LINE_NO, T.PART MAIN_COMPONENT
FROM TRANS_DETAIL T
) X ON X.MAIN_COMPONENT = W.PART AND X.TRX_DATE > T.TRANS_DATE
WHERE T.PART = 'VX112045'
The above query return everything after the first date and that's not what I want. I only want it to return the closest date and that's it.