confusing MySqL JOIN

Question

Unfortunately, I'm not sure if there is a specific name for the query I am attempting to write. My problem is the following, I have created two temporary tables, one with a list of customers who have "opted out" of a communication, either on IVR or through email.

mysql> desc tt_customers;
+------------------+------------------+------+-----+---------+-------+
| Field            | Type             | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+-------+
| id               | int(10) unsigned | NO   | MUL | 0       |       |
| name             | varchar(40)      | NO   |     | NULL    |       |
+------------------+------------------+------+-----+---------+-------+

mysql> desc tt_opt_outs;
+-----------------------+----------------------------------------+------+-----+---------+-------+
| Field                 | Type                                   | Null | Key | Default | Extra |
+-----------------------+----------------------------------------+------+-----+---------+-------+
| customer_id           | int(10) unsigned                       | NO   | MUL | NULL    |       |
| event_type            | enum('PRE_PEAK_TIME','POST_PEAK_TIME'  | YES  |     | NULL    |       |
| notification_channel  | enum('EMAIL','IVR')                    | NO   |     | NULL    |       |
+-----------------------+----------------------------------------+------+-----+---------+-------+

Not all customers in the customer table will be in the opt out table. Customers in the opt out table may be in there with an opt out for EMAIL, IVR or both, and for any event type. I'd like to create a report with the following column headers customer_id, name, IVR Optout, Email Optout, where the IVR and Email opt out columns are regardless of the opt out event_type. I'm not sure how to structure a join/subquery/union or whatever I'd need here to create the exact query I need. Any help would be appreciated!

Answer 1

Apart from the case statement you can also use left outer join.

Query (left outer join)

 select c.id as customer_id , c.name,ti.notification_channel as IVR,
    te.notification_channel as EMAIL from tt_customers c
    left outer join tt_opt_outs ti on c.id = ti.customer_id and ti.notification_channel = 'IVR' 
   left outer join tt_opt_outs te on c.id = te.customer_id and te.notification_channel = 'EMAIL'

Output:

Data Set-Up:

create table tt_customers (id int(10), name varchar(40));
create table tt_opt_outs (customer_id int(10), event_type enum('PRE_PEAK_TIME','POST_PEAK_TIME'), notification_channel enum('EMAIL','IVR') );
insert into tt_customers values (1,"all in");
insert into tt_customers values(2,"email out");
insert into tt_customers values(3,"ivr out");
insert into tt_customers values(4,"all out");
insert into tt_opt_outs values(2,'PRE_PEAK_TIME','EMAIL');
insert into tt_opt_outs values(3,'PRE_PEAK_TIME','IVR');
insert into tt_opt_outs values(4,'PRE_PEAK_TIME','EMAIL');
insert into tt_opt_outs values(4,'PRE_PEAK_TIME','IVR');

SQL Fiddle : http://sqlfiddle.com/#!9/0e82a7/17

Answer 2

Below is the SQL that will give you the desired result to you:

create table tt_customers(id int,name varchar(40));
create table tt_opt_outs(customer_id int,event_type enum('PRE_PEAK_TIME','POST_PEAK_TIME'),notification_channel enum('EMAIL','IVR'));

insert into tt_customers values(1,'ABC');
insert into tt_customers values(2,'XYZ');
insert into tt_opt_outs values(1,'PRE_PEAK_TIME','EMAIL');
insert into tt_opt_outs values(2,'POST_PEAK_TIME','IVR');

Your query for desired result:

select c.id as customer_id,
    c.name,
    case when t.notification_channel = 'IVR' then 'Yes' else null end ivr_optout,
    case when t.notification_channel = 'EMAIL' then 'Yes' else null end email_optout
from tt_customers c
   left join tt_opt_outs t
   on (c.id = t.customer_id);