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
