Confused about how to create a view based on current structure
Edit: Okay so it appears my post perhaps isn't very clear or just to much to read...I did rush it a little. So I've attached my tables from my MySQL database for all to see and these are what I want to JOIN together.
As you can see I'm very new here. In fact I've registered as I could do with the help! I'm not a database designer but have dealt with databases throughout my last 15 years in IT both academically and personally.
I now run my own small IT business (me and my brother) doing repairs, cabling and server installations. To try and help the business run as smoothly as possible I created my own database with an MSAccess front-end and a MySQL back-end.
I've recently purchased what proves to be a fantastic product that will allow me to sync my database contacts to a mailbox on my Exchange server, this will help my business a great deal and save lots of time searching for customer telephone numbers etc.!
I hope you appreciate the background info. I've given and why I've come here.
So...the problem I have is with the design of my database. The 3rd party product requires a flat file style of supplying the contact details to sync with Exchange. The problem is I have this information spread over 3 tables.
Here are ALL the tables in my database:
The 3 that contain the info I need to extract are the first 3 marked with asterisk's.
The system currently works okay and stores almost everything I need. Here's a summary of the function of these 3 tables:
-An 'account' represents a customer or supplier and holds basic info. no addresses and no contact details.
-A customer 'account' can be a business or residential customer. If it is a residential customer there will be at least one 'contact' associated with it more if there are other family members details stored.
-Each 'account' can have many addresses ('addressbook') or 'contacts' associated with it.
-An address can be:
--the main company address
--the directors home address
--another site address
-A contact represents a person
For the 3rd party software I have to create a view (this I can do) but it has to output ALL contact details. So, for example, if in my database I have:
Acme Inc. account header
3 different address: 2 site address and the directors home address
5 contacts: 1 person at each site, the directors wife for home visits and an IT contact.
I know I will end up with more records than I desire in Exchange because I have no relationship between the addressbook and the contact, I am okay to accept that. So based on the above example, in Microsoft Outlook I would expect there to be 3 contacts items stored under the company name each having one of the 3 site addresses and a further 5 contact items under the company name with each of the contacts details.
The only way to do this I believe is to use a union and run a query based on a join of, first the addressbook table and then the contacts table - but this is where I am unsure!
If anyone else can think of a better way of doing this or if I'm doing this in a really awkard way I would be greateful.
Thanks for your time and patience and let me know if theres a major bit of info I've missed!