Joins are one of the biggest stresses in AIMS.
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).
For Oracle, you just make a constraint:
1. alter view <viewname> add constraint <constraintname> primary key (<columnlist>) disable novalidate;
Either way it is a hassle just to connect two tables together in AIMS.
One of the best work-arounds is using FDO Toolbox and MapGuide Maestro to do a Schema Over-ride (thank goodness for Jackie!)
Imagine, I have to use 2 OpenSource Products to fix not 1 but 2 glaring issues with Autodesk Infrastructure Map Server:
"The ability to effectively join two databases together with a common filed (aka a JOIN)" and not being able to select features in a View.
Since the WISHLIST discussion group is now "Archived" I thought issue has best visibility here.