if we set aside Xtraction for a minute. did you achieve the value when you query your database ?
The structure of the nested select appears correct. It is covered up in your screenshot, but when using nested selects as "tables" in Xtraction, you MUST leave the Schema property blank.
Everything you described appears to be correct/complete. Are you receiving any errors? If so, posting the error will help identify the problem.
Is there no error, but the resulting value just comes back blank? That might indicate joining on the wrong field(s).
You can also open a support ticket. I would suggest uploading a copy of your data model file to the ticket. If the support person can't identify the problem, ask them to have me take a look at it.