Create a table from two tables where data is in rows in one table

Question

I have two tables linked by a user ID. I want to create a new table from both which pulls several fields from each table. In one table there is only one row per user ID, in the other there are several rows of data for each user.

The first table is easy - all the data is in one line.
However in the second table the data is arranged in rows where each user ID has several rows. I want to find only four of these rows for each user ID and then insert these into a column in my table. This is the code below but it is not working. Can I nest sub-queries like this?

INSERT INTO new_table
select table1.ID, table1.user_email, table1.display_name, table2.meta-value (where table2.meta_key = ‘pet’), table2.meta-value (where table2.meta_key = ‘color'), table2.meta-value (where table2.meta_key = ‘location), table2.meta-value (where table2.meta_key = ‘house'),
from table_1, table2

score 1 · Answer 1 · answered Aug 24 '16 at 11:40

Try this:

INSERT INTO new_table
(SELECT 
    t1.ID, 
    t1.user_email, 
    t1.display_name, 
    (SELECT meta-value FROM table2 AS t2 WHERE t2.meta_key = 'pet' AND t2.user_id = t1.user_id), 
    (SELECT meta-value FROM table2 AS t2 WHERE t2.meta_key = 'color' AND t2.user_id = t1.user_id), 
    (SELECT meta-value FROM table2 AS t2 WHERE t2.meta_key = 'location' AND t2.user_id = t1.user_id), 
    (SELECT meta-value FROM table2 AS t2 WHERE t2.meta_key = 'house' AND t2.user_id = t1.user_id),
    FROM table_1 AS t1
)

However, its not tested as i don't find further details regarding table schema/data. Hope this helps.

score 1 · Answer 2 · answered Aug 24 '16 at 11:44

You can use queries for joining:

INSERT INTO new_table
select table1.ID, table1.user_email, table1.display_name, table3.meta-value, table4.meta-value, table5.meta-value, table6.meta-value
from table_1 inner join
(select ID, meta_value from table2 where meta_key='pet') as table3 inner join
(select ID, meta_value from table2 where meta_key='color') as table4 inner join
(select ID, meta_value from table2 where meta_key='location') as table5 inner join
(select ID, meta_value from table2 where meta_key='house') as table6;

score 1 · Answer 3 · answered Aug 24 '16 at 12:40

INSERT INTO new_table
select table1.ID, table1.user_email, table1.display_name,
       max(if(table2.meta_key = 'pet', table2.meta-value, NULL)),
       max(if(table2.meta_key = 'color', table2.meta-value, NULL)),
       max(if(table2.meta_key = 'location', table2.meta-value, NULL)),
       max(if(table2.meta_key = 'house', table2.meta-value, NULL))
 from table1
 left join table2 on table2.user_id=table1.user_id
group by table1.ID, table1.user_email, table1.display_name

score 0 · Answer 4 · edited Aug 24 '16 at 15:28

I got it to work. I don't know if this is better or worse than any of the other suggestions.

 INSERT INTO new_table
 SELECT t1.ID,  t1.user_email,  t1.display_name, 
        GROUP_CONCAT(IF(m.meta_key = 'pet_field', m.meta_value, NULL)) AS pet,
        GROUP_CONCAT(IF(m.meta_key = 'color_field', m.meta_value, NULL)) AS color,
        GROUP_CONCAT(IF(m.meta_key = 'location_field', m.meta_value, NULL)) AS location,
        GROUP_CONCAT(IF(m.meta_key = 'house_field', m.meta_value, NULL)) AS house
 FROM wp_users t1,
      wp_usermeta m
 WHERE u.ID = m.user_id
 GROUP BY u.ID;

Create a table from two tables where data is in rows in one table

4 Answers4