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
– Madhur Bhaiya, Commented Oct 7, 2018 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
– Javier Larroulet, Commented Oct 7, 2018 at 17:06

Madhur Bhaiya · Accepted Answer · 2018-10-07 17:09:10Z

2

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

edited Oct 7, 2018 at 17:09

answered Oct 7, 2018 at 17:02

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Poorna Senani Gamage · Accepted Answer · 2018-10-07 16:44:50Z

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 7, 2018 at 16:44

Poorna Senani Gamage

1,2682 gold badges20 silver badges32 bronze badges

Comments

Chayan · Accepted Answer · 2018-10-07 17:08:38Z

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 7, 2018 at 17:08

answered Oct 7, 2018 at 16:59

Chayan

6146 silver badges12 bronze badges

6 Comments

Madhur Bhaiya Over a year ago

@Alex note that left join is really unnecessary. Inner join is sufficient. Left join is inefficient when you deal with bigger tables

Chayan Over a year ago

@MadhurBhaiya, Thanks for your valuable info. I have updated my answer.

Strawberry Over a year ago

@MadhurBhaiya LEFT JOIN and INNER JOIN do different things, which should be the primary reason for choosing to use one over the other.

Madhur Bhaiya Over a year ago

@Strawberry in this particular scenario. left join was unnecessary; that is why suggested that "inner join is sufficient"

Strawberry Over a year ago

@MadhurBhaiya I think the statement that left join is inefficient when you deal with bigger tables is too simplistic an observation

|

Collectives™ on Stack Overflow

Return data from multiple tables by a single query

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related