How to use Join or Union to get the data I need.

Hello,

I need some assistance pulling data from several tables and inserting it into another. I will have dup data and this is not best practice from a 3nf standpoint but I can not change the code so the table schema needs to remain unchanged.

I HAVE A BUNCH OF GROUPS THAT I NEED TO PULL FROM ONE TABLE AND INSERT INTO ANOTHER TABLE FOR A SEPARATE APPLICATION TO ACCESS:

SOURCE TABLE:

describe dbc_bp_groups
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| creator_id   | bigint(20)   | NO   | MUL | NULL    |                |
| name         | varchar(100) | NO   |     | NULL    |                |
| slug         | varchar(200) | NO   |     | NULL    |                |
| description  | longtext     | NO   |     | NULL    |                |
| status       | varchar(10)  | NO   | MUL | public  |                |
| parent_id    | bigint(20)   | NO   | MUL | 0       |                |
| enable_forum | tinyint(1)   | NO   |     | 1       |                |
| date_created | datetime     | NO   |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
9 rows in set (0.001 sec)

DESTINATION TABLE:

describe dbc_jot_groups;
+---------------+-------------+------+-----+---------------------+----------------+
| Field         | Type        | Null | Key | Default             | Extra          |
+---------------+-------------+------+-----+---------------------+----------------+
| jot_groupid   | int(9)      | NO   | PRI | NULL                | auto_increment |
| jot_groupname | varchar(40) | NO   |     | NULL                |                |
| jot_groupdesc | longtext    | YES  |     | NULL                |                |
| jot_ts        | timestamp   | NO   |     | current_timestamp() |                |
+---------------+-------------+------+-----+---------------------+----------------+
4 rows in set (0.001 sec)

THIS WAS EASILY ACHIEVED USING:

insert into dbc_jot_groups (jot_groupid,jot_groupname,jot_groupdesc) select id, name, description from dbc_bp_groups;

NOW I NEED TO TAKE THE USERS IN THE GROUPS AND PUT THEM IN A SEPARATE TABLE WITH THEIR NAME PHONE NUMBER. KEEP IN MIND THAT A USER CAN BE IN MULTIPLE GROUPS AND I NEED TO ITERATE THROUGH EACH GROUP AND GETS ITS MEMBERS, NAME AND PHONE NUMBER TO INSERT INTO THE NEW TABLE.

DESTINATION TABLE

 describe dbc_jot_groupmembers;
+----------------+-------------+------+-----+---------------------+-------------------------------+
| Field          | Type        | Null | Key | Default             | Extra                         |
+----------------+-------------+------+-----+---------------------+-------------------------------+
| jot_grpmemid   | int(9)      | NO   | PRI | NULL                | auto_increment                |
| jot_grpid      | int(9)      | NO   |     | NULL                |                               |
| jot_grpmemname | varchar(40) | NO   |     | NULL                |                               |
| jot_grpmemnum  | varchar(40) | NO   |     | NULL                |                               |
| jot_grpmemts   | timestamp   | NO   |     | current_timestamp() | on update current_timestamp() |
| jot_bbmemid    | int(9)      | YES  |     | NULL                |                               |
+----------------+-------------+------+-----+---------------------+------------------------------

WHERE:

jot_grpid = dbc_bp_groups_members.group_id jot_grpmemname = select value from dbc_bp_xprofile_data where flied_id = 3 and dbc_bp_xprofile_data.user_id = dbc_bp_groups_members.user_id jot_grpmemnum = select value from dbc_bp_xprofile_data where flied_id = 4 and dbc_bp_xprofile_data.user_id = dbc_bp_groups_members.user_id jot_bbmemid = dbc_bp_groups_members.user_id

SOURCE DATA TABELS

MariaDB [devDisciplePlaceCom]> describe dbc_bp_groups_members;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| group_id      | bigint(20)   | NO   | MUL | NULL    |                |
| user_id       | bigint(20)   | NO   | MUL | NULL    |                |
| inviter_id    | bigint(20)   | NO   | MUL | NULL    |                |
| is_admin      | tinyint(1)   | NO   | MUL | 0       |                |
| is_mod        | tinyint(1)   | NO   | MUL | 0       |                |
| user_title    | varchar(100) | NO   |     | NULL    |                |
| date_modified | datetime     | NO   |     | NULL    |                |
| comments      | longtext     | NO   |     | NULL    |                |
| is_confirmed  | tinyint(1)   | NO   | MUL | 0       |                |
| is_banned     | tinyint(1)   | NO   |     | 0       |                |
| invite_sent   | tinyint(1)   | NO   |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+
12 rows in set (0.002 sec)


 describe dbc_bp_xprofile_data;
+--------------+---------------------+------+-----+---------+----------------+
| Field        | Type                | Null | Key | Default | Extra          |
+--------------+---------------------+------+-----+---------+----------------+
| id           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| field_id     | bigint(20) unsigned | NO   | MUL | NULL    |                |
| user_id      | bigint(20) unsigned | NO   | MUL | NULL    |                |
| value        | longtext            | NO   |     | NULL    |                |
| last_updated | datetime            | NO   |     | NULL    |                |
+--------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.001 sec)

To put it another way:

FOREACH dbc_bp_groups_members GET group_id, user_id. then GET group member Meta data from dbc_bp_xprofile_data [ field_id 3 and 4] and INSERT INTO dbc_jot_groupmembers joined on group_id.

the fact that dbc_bp_xprofile_data is row based meta data is throwing me off other wise it would be a fairly straight forward JOIN.

thank you in advance!