#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    17
    Rep Power
    0

    MySQL Join result in single row


    Hi,

    I have 2 tables:
    Customer & Email

    Table Customer:

    id ---- name ---- age
    1 ---- Alex ----- 34
    2 ---- David ---- 26

    and so on...

    Table Email:

    id ---- email ---- related_id
    abc ---- david.k@yahoo.com ---- 2
    sxw ---- d.kir@msn.com ---- 2
    waq ---- kirchoff@aol.com ---- 2
    olj ---- alex@gmail.com ---- 1
    tgd ---- alex.s@hotmail.com ---- 1

    and so on...

    I want to write a query which joins these 2 tables and get me customer information as well as their email addresses.

    My Query:

    PHP Code:
    SELECT <column_namesfrom customer JOIN email ON customer.id email.related_id 
    The problem is that this query returns 3 rows for "David" and 2 rows for "Alex".

    I want a way in which I can get the results in the following format:

    Result:

    id ---- name ---- age ---- email1 ---- email2 ---- email3
    2 ---- David ---- 26 ---- david.k@yahoo.com ---- d.kir@msn.com ----kirchoff@aol.com (first row)

    id ---- name ---- age ---- email1 ---- email2
    1 ---- Alex ---- 34 ---- alex@gmail.com ---- alex.s@hotmail.com (second row)

    How can I achieve this result? Any suggestions?
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,647
    Rep Power
    1945
    With MySql you can take a look at the GROUP_CONCAT function.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2010
    Posts
    17
    Rep Power
    0
    Great, this is what I was looking for. Thanks a lot. You guys are wizards.

IMN logo majestic logo threadwatch logo seochat tools logo