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.
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