views:

41

answers:

2

Hi, I have two tables: data and comments

data:
-------------------
id | name | email |

pics:
-------------------
msg_id | pic |

The data is assosiated by id and msg_id. How I can merge this two tables into final one?

Edit: some rows in data don't have associated a pic, and i need to keep them.

+2  A: 

You may want to use the INSERT INTO ... SELECT syntax:

INSERT INTO final_table
SELECT id, name, email, pic
FROM   data
JOIN   pics ON (pics.msg_id = data.id);

Example:

Your current data:

CREATE TABLE data (id int, name varchar(20), email varchar(100));
INSERT INTO data VALUES (1, 'name1', '[email protected]');
INSERT INTO data VALUES (2, 'name2', '[email protected]');
INSERT INTO data VALUES (3, 'name3', '[email protected]');
INSERT INTO data VALUES (4, 'name4', '[email protected]');

CREATE TABLE pics (msg_id int, pic varchar(100));
INSERT INTO pics VALUES (1, 'pic1.jpg');
INSERT INTO pics VALUES (1, 'pic2.jpg');
INSERT INTO pics VALUES (2, 'pic3.jpg');
INSERT INTO pics VALUES (2, 'pic4.jpg');
INSERT INTO pics VALUES (3, 'pic5.jpg');

Your new table:

CREATE TABLE final_table (
  id int, name varchar(20), email varchar(100), pic varchar(100)
);

INSERT INTO final_table
SELECT      id, name, email, pic
FROM        data
LEFT JOIN   pics ON (pics.msg_id = data.id);

Result:

SELECT * FROM final_table;
+------+-------+---------------+----------+
| id   | name  | email         | pic      |
+------+-------+---------------+----------+
|    1 | name1 | [email protected] | pic1.jpg |
|    1 | name1 | [email protected] | pic2.jpg |
|    2 | name2 | [email protected] | pic3.jpg |
|    2 | name2 | [email protected] | pic4.jpg |
|    3 | name3 | [email protected] | pic5.jpg |
|    4 | name4 | [email protected] | NULL     |
+------+-------+---------------+----------+
6 rows in set (0.00 sec)
Daniel Vassallo
thanks it works!
greenbandit
sorry but now i see a problem, some data in data don't have a picture, and been excluded from merge
greenbandit
@greenbandit: You may want to use `LEFT JOIN` then instead of `JOIN`... Updated my answer with an example.
Daniel Vassallo
seems like is working fine, thanks again.
greenbandit
A: 

Probably you need to fetch data from both table and join them into a single one? So you could use the following query:

SELECT D.*, P.* FROM data D, pics P WHERE D.Id=P.msg_id
Budda