How to select name by 2 fields in another 2 tables in mySQL

Question

I got confused trying after making several searches on google to find a way to do the following:

• I have table called students that contains 3 colums (student_id(primarykey), firstname,lastname),

• the second table called courses contains 2 columns (course code,course_name) and

• the third table called registration contains (student_id,course_code),

I need to select the name of students that are registered in course that includes the word for example "Structures" in the course_name field.

For example (consider the snap-shot)

Regardless the concat of the firstname and lastname, this is easy to do it.

So I will get these student names : SomeOne & GoodStudent

P.S. I created table Registration by inserting from the first two tables together.

So I tried like the following:

select concat(firstname,lastname) 
from students 
  left join registration 
where students.student_id = registration.student_id 
  and registration.course_code like 'Structure%'; 

Answer 1

as correctly mentioned in comments - LEFT JOIN not proper choice in Your case, even if You fix the syntax (JOIN require ON condition)

You need use INNER JOIN:

SELECT 
     CONCAT(s1.firstname,s1.lastname) as full_name 
FROM students s1
INNER JOIN registration r1 ON s1.student_id = r1.student_id 
INNER JOIN courses c1 ON c1.course_code = r1.course_code 
WHERE c1.course_name LIKE '%Structure%'; 

or You need swap tables

SELECT 
     CONCAT(s1.firstname,s1.lastname) as full_name
FROM courses c1 
LEFT JOIN registration r1 ON c1.course_code = r1.course_code
LEFT JOIN students s1 ON s1.student_id = r1.student_id
WHERE c1.course_name LIKE '%Structure%';

as You can see LIKE - must have % from both ends for found what You are looking for