hi,
i have created tables and i want to use a store procedure to get information from almost all of the tables...
lets say i have 4 tables - students, teachers,subjects and presence in classes.
students includes - name+phone+addresas+id
teachers includes - name+phone+degree+address+id and so on..
subjects includes- name+code+hours and so on
presence includes- id_student+id_teacher+code_subject+date and so on
now i want to create a table with all the students name , tachers name , subjects name what are in the presence table.
how can i get all of that information is it possible to write in one select statement
what is right way to do it in a store procedure
thnaks in advanced
select from tables
HBergeron
ok
thanks for the help :)
Bart Coppens
To get all the info, you need for keys between tables to link a row in one table with a row in another, for example, you may have a teacher ID in the student column to say who the students teacher is. Then you can do an inner join between the tables that allows you to pull values from the teacher row which corresponds to the teacher id in the current student row being read.
presence looks like it can be used to join the other tables, like this
select p.date, sj.name as 'Subject', s.name as 'Student', t.name as 'teacher' from presence p
inner join students s on s.id = p.id_student
inner join teachers t on t.id = p.id_teacher
inner join subjects sj on sj.code = p.code_subject
That's off the top of my head, it may not work. But, the general idea is that you use 'inner join' to join tables based on shared values, and give each table a name so you can refer to them by name in your select statement.