Return data from multiple tables by a single query

Question

I have 2 tables, User and Grade.

Table User

Class_ID | Name
100      | Alex
101      | Anna

Table Grade

Class_ID  | Teacher  | Subject  |  Time
  100     |   Join   |  English |  9:00
  101     |   ...    |  Math    |  10:00 

Query all the table User, I run:

SELECT * FROM User WHERE class_ID=100;

Query all the table Grade, I run:

SELECT * FROM Grade WHERE class_ID=100;

How can I return

Name | Class_ID  | Teacher  | Subject  |  Time
Alex |  100      |   Join   |  English |  9:00

with just a single query?

Answer 1

• You can use a simple Inner Join between the two tables.
• It is a good practice to use Aliasing for multi table query.
• User is a Reserved keyword in MySQL. So, it would be better if you can change your table name to something else. Otherwise, you may use backticks (`) around it.
• Do read: Why is SELECT * considered harmful?

Try the following:

SELECT  u.Name, 
        u.Class_ID, 
        g.Teacher, 
        g.Subject, 
        g.Time 
FROM `User` AS u
JOIN Grade AS g 
  ON u.Class_ID = g.Class_ID 
WHERE u.Class_ID = 100