Re: Grouping Question

[YL]

Thanks a lot CJ.  That's the cost of flexibility:-)
  ----- Original Message ----- 
  From: Christian High 
  To: YL 
  Cc: mysql@lists.mysql.com 
  Sent: Sunday, April 22, 2007 12:45 PM
  Subject: Re: Grouping Question



  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