0

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%'; 
  • You need to work a JOIN and then check LIKE – joanolo Apr 23 '17 at 20:17
  • 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. – Leepotchi Apr 23 '17 at 20:26
  • That query is not valid. left join requires on. – ypercubeᵀᴹ Apr 23 '17 at 20:32
  • And you need to join all 3 tables (so two joins, not one). – ypercubeᵀᴹ Apr 23 '17 at 20:32
  • 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? – Leepotchi Apr 23 '17 at 20:35
0

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

  • Thanks very much @a_vlad it works smoothly and as intended. Thank you guys also for trying to help. – Leepotchi Apr 24 '17 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.