Using a SELECT command to retrieve data on MySQL

Question

Say I have three tables - pupils, classes and entries.

Table pupils:
pupil_id (primary key)
first_name
middle_name
last_name
email (unique)
password
reg_date
Table classes:
class_id
subject_name
level_of_entry
exam_board
Table entries:
entry_id (primary key)
exam_date
class_id (foreign key)
pupil_id (foreign key)

Now, for example, let me say that a pupil with the name of Jonathan Parker Smith (pupil_id = 1) was taking a Chemistry exam (class_id = 3) which was 'A2' level_of_entry and part of the 'WJEC' exam board. The entry_id would be 5 and let us say that the exam was to be taken on the 25/06/18. There are other pupils like Jonathan who are taking exams in the 'WJEC' board but with other subjects. How would I create a query script (MySQL) that looks at the 'entries' table and lists the names of all pupils, class name and level of entry for the exams entered with the 'WJEC' exam board? I have done some research and that has led me to believe that I should use a SELECT command...

Any help appreciated, Thanks.

Answer 1

The SELECT starts off by saying "give (select) me these columns" from this table that connects or joins to these other two tables in this way and where this rule is true.

So you figure out what columns names you need for the results.

Then you figure out how to connect all of the pieces together (foreign key here connects to some primary key there).

Then you add a where clause to filter out unwanted data.

Select p.first_name, p.middle_name, p.last_name, 
 c.subject_name, c.level_of_entry
From classes c 
Inner Join entries e on e.class_id = c.class_id
Inner Join pupils p on p.puil_id = e.pupil_id
Where c.exam_board = 'WJEC' 

• Kronixion: Oh I see, thanks. Just wondering, what do the 'Inner join' and 'on' commands mean? Are 'c,e and p' also needed or can I just use the actual table name?
• CaM: inner join tells the database that this new table is connected (joined) to the previous table(s). The part after the on tells the database HOW the join is linked; it defines what connects this new table to the previous table. The c, e, and p are aliases. They are optional, but save typing. But yes, you can use the table names instead of the aliases.