Online SQL Compiler

BEGIN TRANSACTION; /* Create a table called NAMES */ CREATE TABLE hospital(Id integer PRIMARY KEY, Name text); CREATE TABLE medico(Id integer PRIMARY KEY, Name text, idHospital integer, idEspecialidad integer); CREATE TABLE especialidad(Id integer PRIMARY KEY, Name text); /* Create few records in this table */ INSERT INTO hospital VALUES(1,'San fernando'); INSERT INTO hospital VALUES(2,'La paz'); INSERT INTO medico VALUES(1,'Frank', 1, 2); INSERT INTO medico VALUES(2,'Jane', 2, 3); INSERT INTO medico VALUES(3,'Robert', 1, 3); INSERT INTO especialidad VALUES(1,'Brazos'); INSERT INTO especialidad VALUES(2,'Pierna'); INSERT INTO especialidad VALUES(3,'Cabeza'); COMMIT; SELECT e.name, h.name, (select count(m.id) from medico m where m.idEspecialidad = e.id AND m.idHospital = h.id) as counter FROM especialidad e LEFT JOIN medico me on me.idEspecialidad = e.id LEFT JOIN hospital h on h.id = me.idHospital GROUP BY e.name, h.name