On 4/22/07, Christian High <[EMAIL PROTECTED]> wrote:
On 4/22/07, YL <[EMAIL PROTECTED]> wrote: > > I have a contact table looks like > > id type owner_id owner_type value > 11 email 21 person [EMAIL PROTECTED] > 12 phone 21 person 303-777-8888 > 13 PO Box 18 business 220 > 14 cell 21 person 101-202-3344 > ......... > > The reason for such a table is that I get room for any kind of contact > info. even future ones. > > For practical reasons, I need a view from the above to contain only the > following info > > owner_id email phone > > > such that the owner_type = 'person' and column owner_id has no > duplications > > what the sql should be to create such a view? > > thanks a lot As far as I know you will need 3 views to accomplish this view 1 will get the phone number and owner id together and the create statement should look like this CREATE VIEW contact_phone AS SELECT owner_id, value as phone_number FROM contact_info WHERE `type` = 'phone'; view 2 will get the owner_id and the phone number together and should look like this CREATE VIEW contact_email AS SELECT owner_id, value as email_address FROM contact_info WHERE type = 'email'; view 3 will pull the owner_id, email_address, and phone_number together in one row and should look like this CREATE VIEW contact_view AS SELECT contact_phone.owner_id, contact_phone.phone_number as phone_number, contact_email.email_address as email_address FROM contact_phone JOIN contact_email ON (contact_phone.owner_id = contact_email.owner_id); CJ of course i forgot that you wanted only owner_type personal so just add that to the end of the WHERE clause on the contact_phone and contact_email views like
AND owner_type = 'personal' CJ
