-2

I have 6 tables which all contain student information. table names: student details, doctor details, admission details, previous_school, fathers details, mothers details. each of these tables contain an unique ID.

To bring all these tables into one i have a student_info table which contains all the id's from the above 6 tables. these are linked to the unique id's in the corresponding table.

I need to display all student information, i know i would use left join and right join, I just am not sure how mysql reads the id in student_info table and grabs all the information from the corresponding table allowing the user to see all the student information in one form.

thankyou in advance.

BlueDolphin
  • 35
  • 4

2 Answers2

0

EDIT: http://sqlfiddle.com/#!2/c5489/2 (working example)

You don't need to put all of the ids in each table, rather each table would have its own primary key ID - guaranteed to be unique, and you would most likely join each table using an inner join

Table A(user_id int, user_name varchar(255))
Table B(student_id int, student_email varchar(255))

And then select the data you want, by using the relating ID's to join:

Select user_name from TableA
INNER JOIN TableB ON
TableA.user_id = TableB.student_id
What have you tried
  • 11,018
  • 4
  • 31
  • 45
  • each table has its own unique id already. but if i dont have an extra table connecting them all together then how else would i create the relationships? – BlueDolphin Apr 01 '13 at 18:40
  • @abdullaRehmani See my edit with some code example – What have you tried Apr 01 '13 at 18:41
  • that means that all 6 tables would have student id has a field? which is the same as the student_Details table? then i would not need the student_info table. – BlueDolphin Apr 01 '13 at 18:46
  • @abdullaRehmani http://sqlfiddle.com/#!2/c5489/2 – What have you tried Apr 01 '13 at 18:47
  • thanks for that, how do the relationships work then? how would they be connected to each other? or wouldnt they? – BlueDolphin Apr 01 '13 at 18:51
  • @abdullaRehmani It depends on how you're setting your database up. But yes, in order to `join` on values, they must be the same. For example, you may want to find out all students in a teacher's class. So you would have a `student` table, a `teacher` table, and a `student-teacher` table. The student teacher table would have to have student and teacher ID's in it And you would `join` that table based on a common id that you're looking for. – What have you tried Apr 01 '13 at 18:53
  • thankyou for your time. Appreciated – BlueDolphin Apr 01 '13 at 19:03
  • @BlueDolphin My answer had nothing to do with how you should setup your database, rather, how to use Joins. – What have you tried Apr 01 '13 at 19:22
0

You have to specify the joins to make this work:

select * from student, student_info where student_info.student_id = student.id

This tells the server to return all information from both tables, where the student id is matched by the student info's stored student id.

Here, I'm assuming that the student is a 1:many with student_info (where there is at least one, but can be multiple student info rows per student info).

PaulProgrammer
  • 16,175
  • 4
  • 39
  • 56