teste, MySQL - rextester

Language: Layout:

drop table if exists cli, viag, pass;

create table cli (id int, nome varchar(20));
create table viag (id int, titulo varchar(40));
create table pass (id int, id_cliente int, id_viagem int);

insert into cli(id, nome) values
(1, 'bruno'),
(2, 'tamires'),
(3, 'christen wiggins'),
(4, 'justine howard'),
(5, 'anika hammond'),
(6, 'alea vang'),
(7, 'fredericka jensen'),
(8, 'alexa duncan'),
(9, 'lynn mclean'),
(10, 'allegra cantrell');

insert into viag (id, titulo) values
(1, 'gramado'),
(2, 'gramado - mundo magico'),
(3, 'beto carrero'),
(4, 'caminhos rurais');

insert into pass (id, id_cliente, id_viagem) values
(1, 1, 1),
(2, 2, 1),
(3, 3, 1),
(4, 4, 1),
(5, 5, 1),
(6, 6, 2),
(7, 7, 2),
(8, 8, 2),
(9, 9, 2),
(10, 10, 2),
(11, 1, 3),
(12, 2, 3),
(13, 3, 3),
(14, 1, 4),
(15, 2, 4),
(16, 3, 4),
(17, 8, 4),
(18, 9, 4),
(19, 10, 4);

SELECT p.id, c.nome, v.titulo, (select count(*) from pass p2 where p2.id_viagem = p.id_viagem) as total
FROM pass AS p
LEFT JOIN cli AS c ON c.id = p.id_cliente
LEFT JOIN viag AS v ON v.id = p.id_viagem


absolute service time: 0,39 sec