multi-table SELECT from mysql database (return multiple items in single field)

I need some serious开发者_JAVA百科 help. Here's the thing... Assume I have a 'users' table with fields(userId, name). Now I need to grab multiple items of info from other table relating to this user. Some of the items are similar so need to be bundled together.

Now I also have several other tables:

CREATE TABLE app.phones (
`id` INT NOT NULL AUTO_INCREMENT ,
`userId` INT NOT NULL ,
`phone` VARCHAR( 16 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

CREATE TABLE app.emails (
`id` INT NOT NULL AUTO_INCREMENT ,
`userId` INT NOT NULL ,
`email` VARCHAR( 32 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

CREATE TABLE app.contact_methods (
`id` INT NOT NULL AUTO_INCREMENT ,
`userId` INT NOT NULL ,
`method` ENUM( 'phone', 'email' ) NOT NULL,
`methodId` INT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

CREATE TABLE app.groups (
`id` INT NOT NULL AUTO_INCREMENT ,
`groupName` VARCHAR( 16 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

CREATE TABLE app.users_groups (
`id` INT NOT NULL AUTO_INCREMENT ,
`userId` INT NOT NULL ,
`groupId` INT NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

As you might guess, each user can have multiple emails, phones and be part of multiple groups. How would I display a table like below using all the tables above?

+---------+-------------+-------------------+--------------+
|name     |phones       |emails             |groups        |
+---------+-------------+-------------------+--------------+
|John Doe |123-555-0101,|johndoe@mail.com,  |group1,group2,|
|         |123-555-0909 |johndoe2@mail.com  |group3        |
+---------+-------------+---------+---------+--------------+

UPDATE: Emails and Phones should not be taken immediately from their tables. The table contact_methods contains entries which reference those tables.

Try this for size

SELECT u.name, ph.phone, em.email, gr.groupName 
FROM users AS u, phones AS ph, emails AS em, groups AS gr 
WHERE (u.id = ph.userId) 
    AND (u.id = em.userId) 
    AND (u.id = gr.userId) 
GROUP BY u.id

Or to concatenate the values to get one row (thanks @chris-morgan):

SELECT u.name, 
    GROUP_CONCAT(ph.phone SEPARATOR ','), 
    GROUP_CONCAT(em.email SEPARATOR ','), 
    GROUP_CONCAT(gr.groupName SEPARATOR ',') 
FROM users AS u, phones AS ph, emails AS em, groups AS gr 
WHERE (u.id = ph.userId) 
    AND (u.id = em.userId) 
    AND (u.id = gr.userId) 
GROUP BY u.id