CODEWITHSUNDEEP
mysqlsqlselectwhere-clause
fox
fox

Reputation: 39

Select from more than one table mysql

I have a database for exams with tables students, subjects and entries. I am trying to produce a list of all the entries with the OCR exam board, showing the names of the students with entries, the subject names and level of entry for the exams the students are entered for. I know I need to include the WHERE command but also know that I have done this wrong...

Here is my code:

SELECT first_name, last_name, subject_name, level_of_entry
  FROM students, subjects
 WHERE exam_board = 'OCR';

Upvotes: 1

Views: 527

Answers (2)

sagi
sagi

Reputation: 40481

Firstly, when joining tables you need to specify on what relation.

Secondly, avoid the use of implicit join syntax(comma separated) and use the right syntax of a join.

Thirdly, isn't this post the same as: https://stackoverflow.com/questions/35991271/how-do-i-create-a-script-to-show-all-people-taking-the-ocr-exam-mysql ? why two posts.

Any ways, you need to join this three tables together:

SELECT students.first_name,students.last_name,subjects.subject_name,subjects.level_of_entry
FROM students
INNER JOIN entries
 ON(students.student_id = entries.student_id)
INNER JOIN subjects
 ON(entries.subject_id = subjects.subject_id)
WHERE subjects.exam_board='OCR';

Also, as @JoachimIsaksson mentioned, your insert into students table is incorrect, change ` to ' , and lastly when comparing a column to a string use '' like in my where clause.

Upvotes: 3

PSVSupporter
PSVSupporter

Reputation: 348

Replace

SELECT first_name,last_name,subject_name,level_of_entry
FROM students,subjects
WHERE exam_board=OCR;

with

SELECT first_name,last_name,subject_name,level_of_entry
FROM students join entries on
  students.student_id = entries.student_id
              join subjects on
    entries.subject_id =  subjects.subject_id
WHERE exam_board='OCR';

Upvotes: 2

Related Questions