MySQL join dataset on at least X items

My question in a SQL Fiddle.

How can I join all elements in table A with all elements in table B via a many-to-many mapping table, even if no relationship exists? My LEFT JOIN solution isn’t giving the results I expect.

Details:

Given these tables:

CREATE TABLE `user` (
  id INT PRIMARY KEY NOT NULL,
  email VARCHAR(100) NOT NULL
);

CREATE TABLE `event` (
  id INT NOT NULL,
  start_time DATETIME NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE `event_response` (
  id INT PRIMARY KEY NOT NULL,
  user_id INT NOT NULL,
  event_id INT NOT NULL,
  response VARCHAR(5) NOT NULL,
  FOREIGN KEY (user_id)
    REFERENCES `user`(id)
    ON DELETE CASCADE,
  FOREIGN KEY (event_id)
    REFERENCES `event`(id)
    ON DELETE CASCADE
);

And this seed data:

-- create some users
INSERT INTO `user`(`id`, email)
VALUES
  (1, 'abc1@gmail.com'),
  (2, 'abc2@gmail.com'),
  (3, 'abc3@gmail.com');
  
-- create two events
INSERT INTO `event`(`id`, start_time)
VALUES
  (1, '2020-09-01'),
  (2, '2020-10-01');
  
-- Only 3 users have responded to the events
INSERT INTO `event_response`(`id`, user_id, event_id, response)
VALUES
  (1, 1, 1, 'yes'),
  (2, 2, 1, 'no'),
  (3, 3, 2, 'yes');

I need a report like this:

start_time, email, response
"2020-09-01", abc1@gmail.com, yes
"2020-09-01", abc2@gmail.com, no
"2020-09-01", abc3@gmail.com, <NO RESPONSE>
"2020-10-01", abc1@gmail.com, <NO RESPONSE>
"2020-10-01", abc2@gmail.com, <NO RESPONSE>
"2020-10-01", abc3@gmail.com, yes

The query I have tried (but doesn’t give satisfactory results):

SELECT
  e.start_time,
  u.email,
  COALESCE(er.response, '<NO RESPONSE>') AS response
FROM `user` AS u
LEFT JOIN event_response AS er ON u.id = er.user_id
LEFT JOIN `event` AS e ON er.event_id = e.id
ORDER BY e.start_time ASC;

Answer

Use a cross join to generate the rows and left join to bring in the values:

select e.*, u.*, er.response
from event e cross join
     user u left join
     event_response er
     on er.user_id = u.id and er.event_id = e.id;

Advertisement

Answer