I am wanting to have a view table that aggregates rows from four tables.
The first primary table is client and is just a primary key unique id and a profile_id. The second table, profile, holds all of the firstname, address, zipcode kind of information. The third table is email; there can be many email objects to one client object but there is only one email object with the primary column flagged true; the fourth table is phone - this is also a many to one relationship (there can be cellphones, homephones, fax numbers, etc...) and only one object can have the primary column flagged.
JOINing the client table with the profile table is easy as it's one to one; what I want advice on is how to go about selecting an email and phone object based on the primary column (rather than the first JOINed result).
Any resources I can be pointed to would be greatly appreciated; as I have been searching for material for a few days.
Thank you in advance!