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) Example

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%'; 

• joanolo: You need to work a JOIN and then check LIKE
• Leepotchi: Yes I'm using the left join and like actually, But the thing is I'm so confused that the course_name and the student name is in two different tables and registration has id which is common with students table and has course_code which is common with courses table. 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%'; Sorry as I'm junior and I really tried to search using different phrases so that I can find a case similar to mine but failed.
• ypercubeᵀᴹ: That query is not valid. left join requires on.
• ypercubeᵀᴹ: And you need to join all 3 tables (so two joins, not one).
• Leepotchi: Ah I understand, this is just new to me to join 3 tables and it make a sense, So joining 3 tables syntax will be similar to joining 2 tables syntax?

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

• Leepotchi: Thanks very much @a_vlad it works smoothly and as intended. Thank you guys also for trying to help.