MySQL Join two tables and combine multiple rows into one

user_7871668 0
Viewed 4485

I have 2 tables:

sms_recipients

campaign_id | message | user_id | contact_number | status
.........................................................
1            something  12334     078237812719     1
1            something  12123     071231231232     1
2            other      12124     078123123126     0
2            other      12334     078234234212     0
2            other      42124     078124124415     1

sms_campaign

campaign_id | shop_id| campaign_type
.....................................
1             1123     marketing
2             2123     awareness
3             3231     something else
4             4432     bla bla
5             5244     last

campaign_id's are unique for the sms_campaign table, there are multiple user_id's related to the same campaign_id the message is the same for all unique champagne_id

I want to combine them so that every contact_number of the same campaign_id with a status = 0 appears in a single row and column like this:

campaign_id | shop_id| campaign_type | users_mobile_numbers      | message
..........................................................................
1             1123     marketing                                  something 
2             2123     awareness       078123123126,078234234212  other      
3             3231     something else
4             4432     078234234212
5             5244     078124124415

Here is my query so far:

SELECT c.campaign_id, 
shop_id,
campaign_type, 
contact_number AS users_mobile_numbers, 
message FROM sms_campaign c 
LEFT JOIN sms_recipients r 
ON u.campaign_id = c.campaign_id 
WHERE status = 0
mysql sql
2 Answers

In the LEFT JOIN, you will need to either move the filter status = 0 into a join condition, OR if you leave the filter in the WHERE clause, then status = 0 OR status IS NULL to avoid filtering out campaigns with no messages at all - I've done the first option.

As per the comment, you will need to GROUP the data by the campaign columns, and apply aggregate functions to all non-grouped columns, in order to guarantee just one row per group - GROUP_CONCAT will concatenate all text values in each GROUP. I've arbitrarily used MIN to resolve a value for shop and message, but you may need to adjust otherwise. (You can also do a DISTINCT in a GROUP CONCAT, if required).

SELECT 
  c.campaign_id, 
  MIN(shop_id) AS shop_id,
  campaign_type, 
  GROUP_CONCAT(contact_number) AS users_mobile_numbers, 
  MIN(message) AS message
FROM sms_campaign c 
LEFT JOIN sms_recipients r 
  ON u.campaign_id = c.campaign_id AND status = 0
GROUP BY c.campaign_id, campaign_type;
user_3142910

Move the condition on r.status to the ON clause of the outer join. (With the condition in the WHERE clause requiring r.status to be non-NULL, that will negate the outerness of the LEFT JOIN, making it equivalent to an INNER JOIN.)

Add a GROUP BY clause to collapse the rows.

Use a GROUP_CONCAT function to combine the values of contact_number. Column references which appear in the SELECT list but are not included in the GROUP BY clause should also be enclosed in aggregate expressions.)

SELECT c.campaign_id
     , ...

     , GROUP_CONCAT(DISTINCT r.contact_number ORDER BY r.contact_number) AS `c_numbers`

     , MIN(r.message) AS `message`

 FROM campaign c 
 LEFT
 JOIN sms_recipients r 
   ON r.campaign_id = c.campaign_id 
  AND r.status = 0
GROUP BY c.campaign_id

The value returned by GROUP_CONCAT aggregate function is limited by max_group_concat_len variable. Longer values will be silently truncated to the maximum length.

user_1077440
Related