The Ultimate Guide to High Availability with MariaDB

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

2 years, 10 months ago S G
Re: How to use Join or Union to get the data I need.

Apologies for lack of sample data. I am trying to use the pointers you provided to get the below outcome (I added clauses in the sample data selects in order to not show unneeded column data and keep it more readable) :

select group_id,user_id  from dbc_bp_groups_members ;
+----------+---------+
| group_id | user_id |
+----------+---------+
|       16 |      13 |
|       16 |      12 |
|       16 |      11 |
|       16 |      10 |
|       17 |      14 |
|       17 |      15 |
|       17 |      16 |
|       17 |      17 |
|       17 |      18 |
|       17 |      19 |
|       20 |      10 |
|       24 |      14 |
|       24 |      16 |
|       24 |      15 |
|       24 |      17 |
|       24 |      19 |
|       25 |      19 |
|       25 |      14 |
|        1 |      14 |
|       11 |      14 |
+----------+---------+
20 rows in set (0.000 sec)
 
select * from dbc_bp_xprofile_data where user_id > 9 and field_id > 2 AND field_id < 5;
+-----+----------+---------+---------------+---------------------+
| id  | field_id | user_id | value         | last_updated        |
+-----+----------+---------+---------------+---------------------+
|  31 |        3 |      10 | medwards      | 2021-06-24 03:11:59 |
|  34 |        3 |      11 | ledwards      | 2021-06-24 03:11:24 |
|  37 |        3 |      12 | nedwards      | 2021-04-24 14:47:18 |
|  40 |        3 |      13 | iedwards      | 2021-04-24 14:47:52 |
|  43 |        3 |      14 | hutchdad      | 2021-06-21 14:53:08 |
|  46 |        3 |      15 | hutchmom      | 2021-06-24 03:10:58 |
|  49 |        3 |      16 | hutchdaughter | 2021-04-24 16:54:48 |
|  52 |        3 |      17 | hutchson1     | 2021-04-24 16:55:43 |
|  55 |        3 |      18 | hutchson2     | 2021-04-24 16:57:42 |
|  58 |        3 |      19 | hutchson3     | 2021-04-24 16:58:44 |
|  78 |        3 |      25 | demoadmin     | 2021-06-08 02:01:39 |
| 158 |        4 |      14 | 7047047045    | 2021-06-21 14:53:08 |
| 190 |        3 |      58 | dupdup        | 2021-06-23 19:46:19 |
| 191 |        4 |      15 | 7773274355    | 2021-06-24 03:10:58 |
| 193 |        4 |      11 | 4567655645    | 2021-06-24 03:11:24 |
| 195 |        4 |      10 | 2223334545    | 2021-06-24 03:11:59 |
+-----+----------+---------+---------------+---------------------+
16 rows in set (0.000 sec)

FOR EVERY DISTINCT group_id, user_id pair in the dbc_bp_groups_members table I NEED TO GET THE NAME AND PHONE NUMBER FROM the dbc_bp_xprofile_data table and INSERT INTO dbc_jot_groupmembers so that the data looks like this:

select * from dbc_jot_groupmembers;
+--------------+-----------+----------------+---------------+---------------------+-------------+
| jot_grpmemid | jot_grpid | jot_grpmemname | jot_grpmemnum | jot_grpmemts        | jot_bbmemid |
+--------------+-----------+----------------+---------------+---------------------+-------------+
|            1 |        17 | hutchdad       | +17047047045  | 2021-06-15 14:56:19 |        14   |
|            2 |        24 | hutchdad       | +17047047045  | 2021-06-15 19:49:58 |        14   |
|            3 |        25 | hutchdad       | +17047047045  | 2021-06-15 19:49:58 |        14   |
|            4 |        17 | hutchmom       | +17773274355  | 2021-06-15 19:49:58 |        15   |
|            5 |        24 | hutchmom       | +17773274355  | 2021-06-15 19:49:58 |        15   |
|            6 |        16 | ledwards       | +14567655645  | 2021-06-15 19:49:58 |        11   |
|            7 |        16 | medwards       | +12223334545  | 2021-06-15 19:49:58 |        10   |
|            7 |        20 | medwards       | +12223334545  | 2021-06-15 19:49:58 |        10   |

noting that I need to add the "+1" onto the phone number upon insert.

I understand this is duplicate data but until we can find someone to re-write the apps at the application level this is the only solution.

Thank you again in advance,

 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.