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?

Think `INNER JOIN`. Please add sample data and expected output to the question. — Madhur Bhaiya, Oct 07 '18 at 16:43
Give MySQL user guide a read in the `JOIN` section... It'll explain a lot of how to make queries using multiple tables — Javier Larroulet, Oct 07 '18 at 17:06

Madhur Bhaiya · Accepted Answer · 2018-10-07T17:09:10.580

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

score 1 · Answer 2 · answered Oct 07 '18 at 16:44

1

You can use JOIN query. Get an idea from this.

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

answered Oct 07 '18 at 16:44

Poorna Senani Gamage

1,246
2
19
30

Chayan · Answer 3 · 2018-10-07T17:08:38.573

1

SELECT  u.Name, u.Class_ID, g.Teacher, g.Subject, g.Time from User u

INNER JOIN Grade g ON u.Class_ID = g.Class_ID ORDER BY u.Class_ID

edited Oct 07 '18 at 17:08

answered Oct 07 '18 at 16:59

Chayan

604
5
12

@Alex note that `left join` is really unnecessary. Inner join is sufficient. `Left join` is inefficient when you deal with bigger tables – Madhur Bhaiya Oct 07 '18 at 17:06
1

@MadhurBhaiya, Thanks for your valuable info. I have updated my answer. – Chayan Oct 07 '18 at 17:09
@MadhurBhaiya LEFT JOIN and INNER JOIN do different things, which should be the primary reason for choosing to use one over the other. – Strawberry Oct 07 '18 at 18:13
@Strawberry in this particular scenario. `left join` was unnecessary; that is why suggested that "inner join is sufficient" – Madhur Bhaiya Oct 07 '18 at 18:14
1

@MadhurBhaiya I think the statement that `left join is inefficient when you deal with bigger tables` is too simplistic an observation – Strawberry Oct 07 '18 at 18:19
@Strawberry point taken. Unfortunately cannot edit the comment now. – Madhur Bhaiya Oct 07 '18 at 18:20

Return data from multiple tables by a single query

3 Answers3