MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


@@version


8.0.22


create table table1(id int, name char(10));
insert into table1 values (11,'Mic'),(22,'Jeff'),(33,'Tom');

create table table2(id int, note char(10),customerid1 int,customerid2 int,customerid3 int);
insert into table2 values(1,'test',11,11,22),(2,'test',null,33,null)
SELECT t.id, note, t1.name as customer1, t2.name as customer2, 
t3.name as customer3
from table2 t
LEFT JOIN table1 t1 ON t.customerId1 = t1.Id
LEFT JOIN table1 t2 ON t.customerId2 = t2.Id
LEFT JOIN table1 t3 ON t.customerId2 = t3.Id


id
note
customer1
customer2
customer3


1
test
Mic
Mic
Mic

2
test
null
Tom
Tom


CREATE TABLE movies (
  id integer AUTO_INCREMENT primary key,
  name varchar(100)
);

CREATE TABLE customer (
  id integer AUTO_INCREMENT primary key,
  name varchar(100)
);

CREATE TABLE reviews (
  id integer AUTO_INCREMENT primary key,
  rating integer,
  cus_id integer,
  movie_id integer,
  FOREIGN KEY (cus_id) REFERENCES customer(id),
  FOREIGN KEY (movie_id) REFERENCES movies(id)
);

CREATE TABLE blocked(
  id integer AUTO_INCREMENT primary key,
  cus_id integer, -- This is the person blocking
  blocked_cus_id integer, -- This is the person who is blocked
  FOREIGN KEY (cus_id) REFERENCES customer(id),
  FOREIGN KEY (blocked_cus_id) REFERENCES customer(id)
);

INSERT INTO movies (name) VALUES ('up');
INSERT INTO customer (name) VALUES ('bob'), ('james'), ('macy');
INSERT INTO reviews (rating, cus_id, movie_id) VALUES (5, 1, 1), (1, 2, 1), (5, 3, 1);
INSERT INTO blocked (cus_id, blocked_cus_id) VALUES (1, 2)
SELECT m.name,avg(rating) as avg_rating
FROM movies m
JOIN reviews r ON m.id = r.movie_id
join customer c on c.id=r.cus_id
left join blocked b on c.id=blocked_cus_id
group by m.name



name
avg_rating


up
3.6667


SELECT m.name, AVG(r.rating) AS avg_rating
FROM movies m
INNER JOIN reviews r ON m.id = r.movie_id
LEFT JOIN blocked b ON r.cus_id = b.blocked_cus_id
WHERE r.cus_id = 3 AND b.blocked_cus_id IS NULL
GROUP BY m.name


name
avg_rating


up
5.0000


create table schedule(name varchar(20), subject varchar(20));
insert into schedule values('Tina','Math'),('Tina','English'),
('Sam','Geography'),('Sam','English'),('Sam','History'),
('Rafael','English')
select * from
(
select distinct s.subject,s1.name from (select distinct subject from schedule) s cross join schedule s1 
)t left join schedule t1 on t.subject=t1.subject and t.name=t1.name
where t1.name is null and t1.subject is null
order by t.subject



subject
name
name
subject


Geography
Tina
null
null

Geography
Rafael
null
null

History
Tina
null
null

History
Rafael
null
null

Math
Sam
null
null

Math
Rafael
null
null


create table tval(f_id int, t_id int);
insert into tval values(175,176),(175,177),(176,175),(177,175)
SELECT
   p2.f_id,p2.t_id
FROM tval p1
     JOIN tval p2 on p1.f_id=p2.t_id
     and p1.t_id < p2.t_id


f_id
t_id


175
176

175
177
subject	name	name	subject
Geography	Tina	null	null
Geography	Rafael	null	null
History	Tina	null	null
History	Rafael	null	null
Math	Sam	null	null
Math	Rafael	null	null