They rarely work the way you wish and to work around them I have made a lot of JOINS in my spatial databases (Oracle Spatial and SQL Server Spatial) using a VIEW (or stored Query as it is called in MS Access).
My problem is that unless you can put an INDEX on the View or CONSTRAINT you can't select the features in AIMS on the map.
For SQL Server you need to do 2 things:
1. Create view with SHEMABINDING (i.e.. create view with SCHEMABINDING as select * from table
2. Create an index on the unique column in the view.
This does not work if your view points to another Database (for example I integrate with systems such as MAXIMO, TEMPEST, PEOPLESOFT, etc).