MySQL Views: Sub-Joins

Question

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!

Answer 1

There shouldn't really be any problems to joining email and phone too. Just join them in another straight forward join and add WHERE phone.primary = TRUE as an ordinary WHERE-claus.

The following, I think, should do it:

SELECT * 
  FROM client
  JOIN profile
  ON profile.client_id = client.id
  JOIN email
  ON email.client_id = client.id
  JOIN phone
  ON phone.client_id = client.id
WHERE phone.primary = TRUE AND
      email.primary = TRUE

Answer 2

select whatevercolumnsyouwant
  from client
  join profile using (profile_id)
  join email on client.profile_id = email.profile_id
            and email.primary = 1
  join phone on client.profile_id = phone.profile_id
            and phone.primary = 1
where client.id = :whateverclientid

the key point is that ON subclauses of JOIN clauses can specify several conditions (in AND, OR, whatever) just like WHERE clauses can but with a more "specific" purpose!