How to Get Data From Two Tables - PHP and MySQL

Question

I have two tables in MySQL as below:

Table1 as below:

ID (Primary Key Auto Increment) | Student Name | Age

Table2 as below:

Teacher Name | Student ID (This is the Student ID from table1) | Email | Education

Now I want to get data from both the tables table1 and table2 in a single query.

I want to retrieve the values from both tables where Student ID is equals to something.

Student Name | Age | Teacher Name

Can you please let me know how can I query values from two tables as mentioned above. I have read many tutorials but I can not retrieve it properly. I am new to MySQL so please explain clearly. What should I use table join, union, inner or outer join?

How are the 2 tables linked? What is the relationship between the 2? — Sean
– Sean, Commented Nov 10, 2014 at 4:29
i agree with @Sean, if you want to get two tables in a single query, you need at least 1 relationship. for example in your case, you need to add 1 more column in table 1, Student Name | Age | Teacher Name. After that, you can use join query to combine them. — stormrage
– stormrage, Commented Nov 10, 2014 at 4:35

chris · Accepted Answer · 2014-11-10 04:36:08Z

4

If there is no relationship between the tables eg. primary foreign key you should make two separate queries.

If you want to use a join you will need to create the relationship between the tables. Eg a course ID that would link the teachers table to the students table.

That way you could say show me the student and teacher names/ages from course X

edited Nov 10, 2014 at 4:36

answered Nov 10, 2014 at 4:31

chris

2,9714 gold badges43 silver badges48 bronze badges

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

2 Comments

Javaid Over a year ago

I want to retrieve the values from both tables where Student ID is equals to something. I have modified the question.

chris Over a year ago

@Javaid Kamal has the right syntax for a simple join but its still unclear as to what you are after. Does each teacher have only one student? Typically a teacher has multiple students therefore a student id would not belong in a teachers table. Maybe if you explain in plain English what the relationship is it will make more sense.

Kamal · Accepted Answer · 2014-11-10 05:05:31Z

1

Try this tutorial here.

You should implement relation by using foreign keys in your tables.

UPDATED You query should be something like this:

SELECT t1.StudentName, t1.Age, t2.TeacherName
FROM table1 t1, table2 t2
WHERE t1.id = t2.studentid;

edited Nov 10, 2014 at 5:05

answered Nov 10, 2014 at 4:29

Kamal

1615 bronze badges

2 Comments

Javaid Over a year ago

Can you please let me know what is t1 and t2 here?

Jaydip Shingala Over a year ago

The t1 and t2 are known as "alias" You can use JOIN or ALIAS for connect two or more table and get data from the tables.

SVT002 · Accepted Answer · 2014-11-10 11:30:50Z

1

Suppose you have table like Student(Stud_id,Stud_Name,Stud_Age) and another table like Teacher(TeacherName,StudentID,Email,....). Here StdentID should be the foreign key in Teacher, And Stud_Id Should primary key in Student.We are assuming the StudentID and Stud_ID values are same

Then try this,

SELECT Stud_Name, Stud_Age ,TeacherName FROM Student INNER JOIN Teacher ON Stud_id = StudentID

answered Nov 10, 2014 at 11:30

SVT002

513 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to Get Data From Two Tables - PHP and MySQL

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related