Playground: FROM y LEFT JOIN en SQL

Me gustan estos retos, pero lo que mas me cuesta es entender el objetivo. En este caso a mi parecer el reto debería decir algo como:

• Genera una consulta donde se presente el id del curso, el nombre del curso, el id del profesor y el nombre del profesor.
• Re nombra los campos de cursos como: id y name; renombra los campos de teachers como: teacher_id y teacher_name.
• Selecciona solo los campos de cursos donde se tiene un id de teacher relacionado.

Importante: En consola solo debes dejar el código que se pide para la prueba. Elimina los ejemplos pre escritos.

• Dos soluciones validas para el reto:

• Solución 1:

SELECT 
courses.id AS id, 
courses.name AS name, 
teachers.id AS teacher_id,
teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers
ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT null;

• Solución 2:

SELECT 
courses.id AS id, 
courses.name AS name, 
teachers.id AS teacher_id,
teachers.name AS teacher_name
FROM courses
INNER JOIN teachers
ON courses.teacher_id = teachers.id; 

Che, tengo un poco de problema tratando de entender los enunciados de la guía. No es por excusarme, debo mejorar en eso, pero creo que no son tan claros

Mi solucion, por si puede ayudar a alguien 😉

SELECT 
	courses.id as id, 
	courses.name as name, 
	teacher_id, 
teachers.name as teacher_name
FROM courses 
  LEFT JOIN teachers on teachers.course_id = courses.id
where teacher_id is not null;

Por cierto, me encanta esta nueva implementacion de playgrounds con sql 😄

Este es mi solucion
.
.
.
.
.
.

SELECT 
  courses.id as id,
  courses.name as name,
  teachers.id as teacher_id,
  teachers.name as teacher_name
FROM teachers
INNER JOIN courses
ON teachers.id = courses.teacher_id;

Quise probar ponerle un ‘alias’ a cada tabla para hacer más corto el comando y funcionó perfectamente, esa idea la encontré en este video 💚

Acá pueden probar el código:

-- Tu código aquí 👇
SELECT c.id AS id, c.name AS name, t.id AS teacher_id, t.name AS teacher_name
FROM teachers AS t
  INNER JOIN courses AS c
  ON c.teacher_id = t.id;

Esta fue mi solución

SELECT courses.id AS id,
  courses.name AS name,
  teacher_id,
  teachers.name AS teacher_name
FROM courses
  LEFT JOIN teachers ON courses.id = teachers.course_id
WHERE teacher_id is NOT NULL;

Solución… 😄
.

.
.
.
.
.
.

SELECT c.id, c.name, t.id AS teacher_id, t.name AS teacher_name
FROM courses AS c
INNER JOIN teachers AS t ON c.teacher_id = t.id;

Antes de hundir en Correr Pruebas, elimina las 2 sentencias SQL que vienen por defecto en el playground, de lo contrario marca error…

La guía no es muy clara, toma más esfuerzo entender lo que pide que armar el query. Tampoco los mensajes de error dan mucho detalle, solo dice que lo intentes de nuevo.

Les paso mi solución al reto. Como a muchas personas, también me costó un poquitín entender la guía, así que me tardé bastante en lograrlo, además tuve que leer mis notas una y otra vez para entender mejor lo de JOIN. Leyendo los comentarios vi que existen varias formas de solucionar esto, así que si tienen una diferente a la mía, pásenla para revisarla! 😃

SELECT courses.id AS id, 
courses.name AS name, 
teachers.id AS teacher_id, 
teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers
ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;

SELECT 
  courses.id AS id,
  courses.name AS name,
  courses.teacher_id AS teacher_id,
  teachers.name AS teacher_name
  
FROM courses 
INNER JOIN teachers ON courses.teacher_id = teachers.id;

Completed, mas dificil que los demás!

Playground FROM y LEFT JOIN en SQL.JPG pero se logro !!

select * from courses
join teachers on courses.id = teachers.course_id
where teacher_id is NOT NULL;

SELECT
  c.id AS id,
  c.name AS name,
  c.teacher_id AS teacher_id,
  t.name AS teacher_name
FROM courses AS c
	LEFT JOIN teachers AS t
	ON c.teacher_id = t.id
WHERE c.teacher_id IS NOT NULL;

Les propongo una alternativa por si no les gusta mucho escribir tanto “courses” y “teacher”

SELECT c.id, c.name, c.teacher_id, t.name AS teacher_name
FROM courses AS c
LEFT JOIN teachers AS t ON c.teacher_id = t.id
WHERE c.teacher_id IS NOT NULL;

SELECT courses.id, courses.name, teacher_id, teachers.name AS teacher_name
FROM courses
  LEFT JOIN teachers ON courses.teacher_id = teachers.id
WHERE teacher_id IS NOT NULL;

-- Tu código aquí 👇
SELECT
  courses.id AS id,
  courses.name AS name,
  courses.teacher_id AS teacher_id,
  teachers.name AS teacher_name
FROM
  courses
INNER JOIN
  teachers
ON
  courses.teacher_id = teachers.id
WHERE
  courses.teacher_id IS NOT NULL
```;

Es complicado de entender la guía, hay que leerla detenidamente y frase a frase comprenderlo y aplicarlo en consulta. Con la oración: ‘No tengas en cuenta si el profesor tiene curso asociado, únicamente si el curso tiene profesor asociado’

Entiendo que están solicitando INNER JOIN en lugar de buscar una condición, puesto que a estas alturas del curso aún no se nos ha dado detalle del WHERE.

En cuanto a AS, solo es necesario teacher_name para no confundir name (curso) name (profesor), para el resto de información por defecto el descriptivo de la tabla es único. Espero les sea de utilidad.

-- Tu código aquí 👇
select courses.id,
courses.name,
courses.teacher_id, 
teachers.name as teacher_name
FROM courses 
inner join teachers 
on courses.teacher_id = teachers.id;

SELECT
courses.id AS "ID Curso",
courses.name AS "Nombre curso",
courses.teacher_id AS "Teacher ID",
teachers.name AS "Nombre Profe"
FROM courses
LEFT JOIN teachers ON courses.teacher_id=teachers.id
WHERE courses.teacher_id IS NOT NULL;

Otra solución:

select courses.id as id_curso, courses.name as nombre_curso , teachers.id as id_profesor , teachers.name as nombre_profesor from courses LEFT JOIN teachers on courses.teacher_id = teachers.id where teachers.id = 8 or teachers.id = 16 or teachers.id = 30 or teachers.id = 1  group by courses.name;

Mi solución

select *
from teachers
INNER JOIN courses on teachers.id=courses.teacher_id;

select 
c.id as id.profesor
c.name as nombre_profesor
from courses c
left join teachers  t on c.id=t.course_id
WHERE teacher_id is NOT NULL;

Mi solucion, por si puede ayudar a alguien ✨

SELECT courses.id AS id, courses.name AS name, 
teachers.id AS teacher_id  , teachers.name AS teacher_name
FROM teachers 
	  JOIN courses
    ON teachers.course_id = courses.id AND 
    courses.teacher_id = teachers.id ;

Dificil de entender el enunciado… Está un poco enredado

Mi solución fue esta, aunque me la marca mal, tiene el mismo resultado.

SELECT c.id, c.name, c.teacher_id, t.name
FROM courses as c
INNER JOIN teachers as t
ON c.teacher_id = t.id;

Mi solución 😊

SELECT courses.id, 
courses.name, 
teachers.id AS teacher_id, 
teachers.name AS teacher_name 
FROM courses
INNER JOIN teachers 
ON courses.teacher_id = teachers.id;

Select
courses .id AS CodCurso,
courses .name AS NombreCurso,
teachers .id AS IdProfe,
teachers .name AS NombreProfe
from courses
left join teachers ON
courses. teacher_id = teachers. id
WHERE courses. teacher_id IS NOT null;

Hice el ejercicio con INNER JOIN en vez de LEFT JOIN y me da el mismo resultado, sólo que si utilizo INNER JOIN no pasa la prueba

.
.
.
.
.
.
.
.
.

SELECT courses.id AS id, 
       courses.name AS name,
       courses.teacher_id AS teacher_id,
       teachers.name AS teacher_name
FROM courses
JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;

Mi solucion

SELECT courses.id as id,
courses.name as name,
teachers.id as teacher_id,
teachers.name as teacher_name
FROM teachers 
LEFT JOIN courses
ON teachers.id = courses.teacher_id
WHERE teacher_id IS NOT NULL;

Hice una cosa bien extraña, pero funcionó jajajaj

SELECT 
  courses.id AS id,
  courses.name AS name,
  teachers.id AS teacher_id,
  teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL
UNION
SELECT 
  courses.id AS id,
  courses.name AS name,
  teachers.id AS teacher_id,
  teachers.name AS teacher_name
FROM courses
RIGHT JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;

La verdad está tan confusa la guia que prefiero aprender más y ya intentar mis querys con mis propios proyectos xd

37. Playground: FROM y LEFT JOIN en SQL

-- Tu código aquí 👇
SELECT courses.id AS id, courses.name AS name, courses.teacher_id AS teacher_id, teachers.name AS teacher_name
FROM courses
INNER JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;

Prueba superada

– Mi solución 👇

SELECT courses.id AS id,
courses.name AS name,
teachers.id AS teacher_id,
teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers 
ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;

Reutlizando alias teacher_id en la sentencia ON y WHERE.

SELECT
  courses.id,
  courses.name,
  courses.teacher_id AS teacher_id,
  teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers 
ON teacher_id = teachers.id
WHERE teacher_id IS NOT NULL;

Adjunto comparto solucion:
.
Ejecutando comandos:
.

.
Corriendo pruebas:
.

Esta fue mi solucion, con la menor cantidad de lineas
.
.
.
.
.
.
.
.

SELECT courses.id, courses.name, courses.teacher_id,  teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers ON courses.id = teachers.course_id
WHERE courses.teacher_id IS NOT NULL;

Mando solución👇
*
*
*
*
*
*
*
*
*
*
*
*
*
*

SELECT
  courses.id AS id,
  courses.name AS name,
  courses.teacher_id AS teacher_id,
  teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;

Hola, adjunto el desarrollo de la actividad.

Comparto mi codigo

SELECT c.id,c.name,c.teacher_id,t.name AS teacher_name
    FROM courses AS c
    LEFT JOIN teachers AS t ON c.id = t.course_id
    WHERE c.teacher_id IS NOT NULL;

Playground FROM y LEFT JOIN en SQL:

Mi solución:

.
.
.
.

SELECT 
courses.id, courses.name, courses.teacher_id, teachers.name as teacher_name  from courses
LEFT JOIN teachers ON teachers.id = courses.teacher_id
WHERE teacher_id is NOT NULL;

Mi solución
.
.
.
.
.
…
.
.
…
.

[<SELECT
  courses.id AS id,
  courses.name AS name,
  courses.teacher_id AS teacher_id,
  teachers.name AS teacher_name
FROM courses
LEFT JOIN teachers ON courses.teacher_id = teachers.id
WHERE courses.teacher_id IS NOT NULL;
> 

Solucion!

SELECT 
c.id as id,
c.name as name,
t.id as teacher_id,
t.name as teacher_name
FROM teachers t
JOIN courses c ON t.id = c.teacher_id;

Mi solución 😃

SELECT courses.id,
courses.name, 
teachers.id AS teacher_id,
teachers.name AS teacher_name 
FROM courses 
INNER JOIN teachers 
ON 
courses.teacher_id = teachers.id;

![](

SELECT
  courses.id AS id,
  courses.name AS name,
  teachers.id AS teacher_id,
  teachers.name AS teacher_name
FROM courses
INNER JOIN teachers
ON courses.teacher_id = teachers.id;