Repeat all base table rows for EACH and don’t aggregate the NULL from the join table

join;

I have 2 table below

Feedback Type

+-------------+----------------+
| feedback_id | feedback_desc  |  
+-------------+----------------+
| 1           | Name           |
| 2           | Address        |  
| 3           | Phone          |  
+-------------+----------------+

Customer Feedback

+-------------+-------------+----------------+
| customer_id | feedback_id |  given_answers |  
+-------------+-------------+----------------+
| 1           | 1           | John Smith     |
| 1           | 2           | 123, New York  | 
| 1           | 3           | 07784954555    | 
| 2           | 1           | Jamie Plant    |
| 3           | 1           | Steve Brown    |
| 3           | 3           | 07808987767    |
+-------------+-------------+----------------+

I like the result as below after table joining

+-------------+-------------+---------------+----------------+
| customer_id | feedback_id | feedback type | given_answers  |  
+-------------+-------------+---------------+----------------+
| 1           | 1           | Name          | John Smith     |
| 1           | 2           | Address       | 123, New York  | 
| 1           | 3           | Phone         | 07784954555    | 
| 2           | 1           | Name          | Jamie Plant    |
| 2           | 2           | Address       | NULL           | 
| 2           | 3           | Phone         | NULL           |
| 3           | 1           | Name          | Steve Brown    |
| 3           | 2           | Address       | NULL           |
| 3           | 3           | Phone         | 07808987767    |
+-------------+-------------+---------------+----------------+

Any idea how can I do that?

Best Answer

You need to create all customer_id/feedback_id combinations first and then left join to it.

Assuming there's a customer table with unique ids:

select x.customer_id, x.feedback_id, x.feedback_desc, cf.given_answers
from
 (
   select c.customer_id, f.feedback_id, f.feedback_desc
   from feedback as f
   cross join customer as c -- or (select distinct customer_id 
                            --     from customer_feedback) as c 
 ) as x
left join customer_feedback as cf
  on x.customer_id = cf.customer_id
 and x.feedback_id = cf.feedback_id ;

or, without a derived table:

select c.customer_id, f.feedback_id, f.feedback_desc, cf.given_answers
from feedback as f
  cross join customer as c -- or (select distinct customer_id 
                           --     from customer_feedback) as c 
  left join customer_feedback as cf
    on  c.customer_id = cf.customer_id
    and f.feedback_id = cf.feedback_id ;
Related Question