Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- data:
- -------------------
- id | name | email |
- pics:
- -------------------
- msg_id | pic |
- INSERT INTO final_table
- SELECT id, name, email, pic
- FROM data
- JOIN pics ON (pics.msg_id = data.id);
- CREATE TABLE data (id int, name varchar(20), email varchar(100));
- INSERT INTO data VALUES (1, 'name1', 'a@example.com');
- INSERT INTO data VALUES (2, 'name2', 'b@example.com');
- INSERT INTO data VALUES (3, 'name3', 'c@example.com');
- INSERT INTO data VALUES (4, 'name4', 'd@example.com');
- 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');
- 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);
- SELECT * FROM final_table;
- +------+-------+---------------+----------+
- | id | name | email | pic |
- +------+-------+---------------+----------+
- | 1 | name1 | a@example.com | pic1.jpg |
- | 1 | name1 | a@example.com | pic2.jpg |
- | 2 | name2 | b@example.com | pic3.jpg |
- | 2 | name2 | b@example.com | pic4.jpg |
- | 3 | name3 | c@example.com | pic5.jpg |
- | 4 | name4 | d@example.com | NULL |
- +------+-------+---------------+----------+
- 6 rows in set (0.00 sec)
- SELECT D.*, P.* FROM data D, pics P WHERE D.Id=P.msg_id
Add Comment
Please, Sign In to add comment