Fetch fields from two tables in mysql

Question

I have two tables, one which stores the registered students, lets call it students_register. The second table keeps examination details of the students, lets call it exam_details. In the students_register table, i store:

student registration number    
first name    
last name    
email_address    
date_of_birth

and other details.

In the exam_details table, i store the registration number and the students marks in the different subjects.

Now, the challenge is that i want to query the exam_details table and display the data in a table but instead of displaying the students registration number, i want to associate the registration number in the exam_details table to that in the students_register table so that i can display the student's names instead of the registration number.

How can i go about this?

1. students_register table

id reg_number first_name  last_name  email_address      dob
1   P2894      John        Smith       [email protected]   12/05/1990

2. exam-details table

id reg_number english maths chemistry  biology physics
1  P2894       60%    80%    50%         72%     64%

How do i display this data in a table such that i have

first_name last_name  english  maths  chemistry  biology physics
 John       Smith      60%      80%    50%        72%    64%

what about mysql join tables -dev.mysql.com/doc/refman/5.0/en/join.html - from your sample key would be reg_number like <<SELECT sr.first_name, sr.last_name, ed.english, ed.maths, ed.chemistry, ed.biology, ed.physics from [exam-details] ed LEFT JOIN [students_register] sr ON sr.reg_number = ed.reg_number;>> — Anda Iancu
– Anda Iancu, Commented Apr 17, 2013 at 8:14

Ivan Yonkov · Accepted Answer · 2013-04-17 08:12:44Z

2

SELECT tb2.first_name, tb2.last_name, tb1.english, tb1.maths, tb1.chemistry, tb1.bilogy, tb1.physics
FROM exam_details AS tb1
INNER JOIN students_register AS tb2
ON tb1.reg_number = tb2.reg_number

Take a look at SQL Joins -> http://www.w3schools.com/sql/sql_join.asp

answered Apr 17, 2013 at 8:12

Ivan Yonkov

7,0291 gold badge20 silver badges25 bronze badges

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

Comments

Niek van der Steen · Accepted Answer · 2013-04-17 08:12:28Z

1

With a simple JOIN query:

SELECT id, student_register.reg_number, first_name, 
       last_name, english, maths, chemistry, etc 
FROM student_register
JOIN exam-details ON student_register.reg_number = exam-details.reg_number

answered Apr 17, 2013 at 8:12

Niek van der Steen

1,44311 silver badges33 bronze badges

Comments

S.Visser · Accepted Answer · 2013-04-17 08:13:05Z

1

There is no need to use JOIN here. I think this is the easiest way.

SELECT 
  sr.first_name, 
  sr.last_name, 
  ed.english, 
  ed.maths, 
  ed.chemistry, 
  ed.biology, 
  ed.physics 
FROM 
  students_register sr, 
  exam_details ed 
WHERE 
  sr.reg_number = ed.reg_numver

answered Apr 17, 2013 at 8:13

S.Visser

4,7751 gold badge25 silver badges48 bronze badges

2 Comments

Niek van der Steen Over a year ago

This is a join, too. Just another way of writing it. It will result in the same execution plan as a 'regular JOIN'.

mutiemule Over a year ago

Have actually learnt a new way of joining tables. Your answer works well, Thanks indeed.

bwoebi · Accepted Answer · 2013-04-17 08:14:02Z

1

See for JOIN.

SELECT first_name, last_name, english, maths, chemistry, biology, physics FROM exam_details AS ex JOIN students_register st ON (ex.reg_number = st.reg_number)

answered Apr 17, 2013 at 8:14

bwoebi

23.8k5 gold badges64 silver badges81 bronze badges

1 Comment

mutiemule Over a year ago

This works well too. Thanks for the immediate answers. Am overwhelmed.

zkanoca · Accepted Answer · 2013-04-17 08:15:28Z

1

SELECT * FROM student-register INNER JOIN exam-details ON student-detail.reg_number=exam-details.reg_number

may be your query. Then you print out whatever you like.

answered Apr 17, 2013 at 8:15

zkanoca

10k10 gold badges55 silver badges99 bronze badges

Comments

gorzan · Accepted Answer · 2013-04-17 08:12:14Z

0

select first_name, last_name, english, maths, chemistry, biology, physics
from students_register, exam_details
where students_register.reg_number=exam_details.reg_number

answered Apr 17, 2013 at 8:12

gorzan

6741 gold badge5 silver badges11 bronze badges

Comments

Marcassin · Accepted Answer · 2013-04-17 08:12:38Z

0

You have to join these 2 tables

SELECT s.first_name, s.last_name, e.english, e.maths, e.chemistry; e.biology, e.physics FROM students_register s LEFT JOIN exam_details e USING (reg_number)

answered Apr 17, 2013 at 8:12

Marcassin

1,4051 gold badge14 silver badges23 bronze badges

Comments

Pank · Accepted Answer · 2013-04-17 08:17:02Z

0

May be this will help u.

select students_register.first_name,students_register.last_name,exam-details.english, exam-details.maths, exam-details.chemistry, exam-details.biology, exam-details.physics from exam-details left join students_register on students_register.reg_number=exam-details.reg_number

answered Apr 17, 2013 at 8:17

Pank

14.4k10 gold badges35 silver badges46 bronze badges

Collectives™ on Stack Overflow

Fetch fields from two tables in mysql

8 Answers 8

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related