How do I import Many to Many relationships from MySQL via ODBC?
I am using the Actual ODBC commercial driver to connect to a MySQL 5.05 database so I can read the MySQL db in Filemaker Pro 10 Advanced on my Intel Mac mini running OS X Leopard 10.5.7. My goal is to have the whole MySQL database reflected in my Filemaker file so I can more easily query and generate reports from the information we store on the server.
While the Filemaker help file is generally useful in some areas, I can't find instructions for how to import/connect to a multi-table MySQL database that has many "Many to Many" joins in it and over 50 tables (including about 20 look ups). Should each table be imported separately and then joined through relationships or code (and then one table at a time or all tables together), or some other way? The External SQL Source doesn't seem to allow for this, but Many to Many joins are so fundamental to databases I can't believe it is impossible to do... so, how?
One example of such a relationship is:
Relational Table: rClientAddress
This arrangement allows one client to have many addresses, and many people to have the same address (eg each member of a family, people living in multi-occupancy buildings etc). We have many other instances of similar setups in the database, and with some 500 clients and 300 addresses Value Lists are out of the question. Can I use the External SQL Source feature, or do I need to use some sort of script?
All suggestions welcomed.