This question would probably be best placed on the Mapviewer forum: https://community.oracle.com/community/developer/english/fusion_middleware/application_server/mapviewer
What happens when you run this query? Do you get an error? If so what error? Maybe try putting this query in a view instead.
why you don't use a view on a your sql statment?
How do you use a view on the sql statement.
It is easy enough to set up a view between the two tables, then just make sure that you have added geometry metadata for the view, and of course make sure that the geometry is indexed. Once this is done, you should be able to see the view from with MapBuilder by clicking on the "show data" button and then expanding the geometry tables within the tree.