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%';
Best Answer
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:
or You need swap tables
as You can see LIKE - must have % from both ends for found what You are looking for