view in which each column contains data from a different table
-
I am doing a student attendance project in php & mysql. I have a monthly table for recording attendance of students. the first column in the table is the roll number which is common for all the tables and the dates of each month have a column of their own. suppose if the attendance is of 5 months, I have 5 tables of the same repeated table. I want to create a table or view that has columns for showing total of each month with the students roll number i.e. student roll number column, first month total column, second month total column, so on. Can anyone please help me how to do it in mysql?
-
I am doing a student attendance project in php & mysql. I have a monthly table for recording attendance of students. the first column in the table is the roll number which is common for all the tables and the dates of each month have a column of their own. suppose if the attendance is of 5 months, I have 5 tables of the same repeated table. I want to create a table or view that has columns for showing total of each month with the students roll number i.e. student roll number column, first month total column, second month total column, so on. Can anyone please help me how to do it in mysql?
Member 11840363 wrote:
if the attendance is of 5 months, I have 5 tables of the same repeated table.
Let me get this clear, every month you create a new table for attendances. In which case you should start again and design your data structure to be sustainable, that design is absolutely NOT. You need just 1 table StudentID (roll number) Date If you have a table for each month then whatever you create you are going to have to redo every month unless the tables are year independent IE tblJan, tblFeb etc. This is still a really bad design but can be worked with under duress. You can create a view using UNION and a select statement for each month table but you will need to add another select every month.
Never underestimate the power of human stupidity RAH
-
Member 11840363 wrote:
if the attendance is of 5 months, I have 5 tables of the same repeated table.
Let me get this clear, every month you create a new table for attendances. In which case you should start again and design your data structure to be sustainable, that design is absolutely NOT. You need just 1 table StudentID (roll number) Date If you have a table for each month then whatever you create you are going to have to redo every month unless the tables are year independent IE tblJan, tblFeb etc. This is still a really bad design but can be worked with under duress. You can create a view using UNION and a select statement for each month table but you will need to add another select every month.
Never underestimate the power of human stupidity RAH
Thanks for taking out some time to reply to my query. I have tried Union and select statements but I am not getting the desired result. It would be of great help if you show me some snippet or syntax of the query for example joining 4 tables using union and select. For the tables creation part I was thinking about creating procedures to create a table whenever needed, and at the time of creation the name of the table to be created and the table it references should be asked. It would be great help if you could help with the above both.
-
Thanks for taking out some time to reply to my query. I have tried Union and select statements but I am not getting the desired result. It would be of great help if you show me some snippet or syntax of the query for example joining 4 tables using union and select. For the tables creation part I was thinking about creating procedures to create a table whenever needed, and at the time of creation the name of the table to be created and the table it references should be asked. It would be great help if you could help with the above both.
Member 11840363 wrote:
For the tables creation part I was thinking about creating procedures to create a table
NO! just don't go down this path, redesign you data to have only 1 table. Seriously you are making a HUGE mistake if you do not structure your data properly. If you are able to write procedures then I assume you own the database. Restructure NOW before you do any more work. You need 1 table with a min of 2 fields RollNo and date.
Never underestimate the power of human stupidity RAH
-
Member 11840363 wrote:
if the attendance is of 5 months, I have 5 tables of the same repeated table.
Let me get this clear, every month you create a new table for attendances. In which case you should start again and design your data structure to be sustainable, that design is absolutely NOT. You need just 1 table StudentID (roll number) Date If you have a table for each month then whatever you create you are going to have to redo every month unless the tables are year independent IE tblJan, tblFeb etc. This is still a really bad design but can be worked with under duress. You can create a view using UNION and a select statement for each month table but you will need to add another select every month.
Never underestimate the power of human stupidity RAH
-
Thanks for taking out some time to reply to my query. I have tried Union and select statements but I am not getting the desired result. It would be of great help if you show me some snippet or syntax of the query for example joining 4 tables using union and select. For the tables creation part I was thinking about creating procedures to create a table whenever needed, and at the time of creation the name of the table to be created and the table it references should be asked. It would be great help if you could help with the above both.
Listen to the man, seriously, it's the best advice you'll ever get regarding databases.
Wrong is evil and must be defeated. - Jeff Ello
-
Listen to the man, seriously, it's the best advice you'll ever get regarding databases.
Wrong is evil and must be defeated. - Jeff Ello
Thanks a lot guys for your valuable time. I realize that it is a complicated design to manage and not that convenient either for maintaining. but can anyone help me the possible solutions that would help in such a scenario for knowledge purpose.
-
Thanks a lot guys for your valuable time. I realize that it is a complicated design to manage and not that convenient either for maintaining. but can anyone help me the possible solutions that would help in such a scenario for knowledge purpose.
-
Thanks a lot guys for your valuable time. I realize that it is a complicated design to manage and not that convenient either for maintaining. but can anyone help me the possible solutions that would help in such a scenario for knowledge purpose.
Don't you get the impression you may have done something incorrectly, the only response you have had is to fix your data structure. As Eddy said once yo have done that the query becomes trivial. It is possible to hack around the terrible structure you have built but us showing you for any reason is doing you a disservice. We are here to help you learn, help fix problems, we are not interested in perpetrating bad design.
Never underestimate the power of human stupidity RAH
