How can I flatten results from left join using MySQL?

Question

I'm trying to create a query where I join a USER table with an ADDRESS_BOOK table in which 1 user may have multiple addresses (i.e. billing, shipping). Each address is its own entry in the ADDRESS_BOOK table.

Each entry in the ADDRESS_BOOK table has a USER_ID value that corresponds to one entry in the USER table.

I would like to get the following results:

____________________________
USER ID | BILLING | SHIPPING

Right now I get two results per USER_ID, one for the SHIPPING address, and one for the BILLING address. I need to get one result per USER_ID where the SHIPPING and the BILLING information (the two query results) are flattened.

Answer 1

Use GROUP BY to group the rows by the user ID and then GROUP_CONCAT to get all the entries as a single row. With the following example (where I use fullname instead of user ID just to make it easier on the eye):

CREATE TABLE user (
       user_id INT AUTO_INCREMENT PRIMARY KEY,
       fullname VARCHAR(32)
);

CREATE TABLE billing (
       user_id INT,
       billing VARCHAR(128)
);

CREATE TABLE shipping (
       user_id INT,
       shipping VARCHAR(128)
);

INSERT INTO user(fullname) VALUES ('Susan');
SET @last_id = LAST_INSERT_ID();
INSERT INTO billing VALUES
       (@last_id, 'Box 123');
INSERT INTO shipping VALUES
       (@last_id, '123 Oak Street'),
       (@last_id, '234 Pine Street');
INSERT INTO user(fullname) VALUES ('Mike');
SET @last_id = LAST_INSERT_ID();
INSERT INTO billing VALUES
       (@last_id, 'Box 12'),
       (@last_id, 'Deep Mine 3');
INSERT INTO shipping VALUES
       (@last_id, '4711 Iron Road');

This select statement

SELECT fullname
     , group_concat(shipping)
     , group_concat(billing)
  FROM user JOIN billing USING (user_id)
            JOIN shipping USING (user_id)
GROUP BY fullname;

Will give this result:

+----------+--------------------------------+--------------------+
| fullname | Shipping                       | Billing            |
+----------+--------------------------------+--------------------+
| Mike     | 4711 Iron Road,4711 Iron Road  | Box 12,Deep Mine 3 |
| Susan    | 234 Pine Street,123 Oak Street | Box 123,Box 123    |
+----------+--------------------------------+--------------------+
2 rows in set (0.00 sec)

Answer 2

This might help:

SELECT U.USER_ID,  
(SELECT ADDRESS FROM ADDRESS_BOOK WHERE USER_ID = U.USER_ID AND ADDRESS_TYPE = 'BILLING') AS BILLING,  
(SELECT ADDRESS FROM ADDRESS_BOOK WHERE USER_ID = U.USER_ID AND ADDRESS_TYPE = 'SHIPPING') AS SHIPPING 
FROM USER U;