MySQL

[nadnakinam]

Hi to all,

i need a help

I have 2 tables namely subjects & marks.
Subjects has 2 columns namely subject_id (PK), subject_name & class_id (FK).
Marks has mark_id, student_id(FK), exam_id (FK), sub_1, sub_2, ... sub_n

here sub_1, sub_2, ... sub_n are columns in 'Marks' table but actually they are records in 'Subjects' table

I need to select the subjects by
'SELECT subject_name FROM subjects WHERE class_id = 1;'
then
'SELECT sub_1, sub_2, ... sub_n FROM marks WHERE exam_id = 1 AND student_id = 1;'

is there any way to combine these two queries like
'SELECT (SELECT subject_name FROM subjects WHERE class_id = 1) FROM marks WHERE exam_id = 1 AND student_id = 1;'
-- I knew that this query is wrong but expect the result some thing like that So that i can have the marks of a particular student.

Thanks

[debasisdas]

But i do not understand why you need to combine two SQL statements.

If you want to fetch records from both the tables then you need to join both the tables.

[nadnakinam]

If you want to fetch records from both the tables then you need to join both the tables.

here I'm not fetching records from two table, from 1st table (i.e., subjects) i'm getting subjects of particular class =1. (sample output: Maths, Physics, Chemistry)
But in the 2nd table (i.e., marks) they (records obtained from 1st query) are columns not records. (Herein table2 Maths, Physics, Chemistry are column names).

Thanks

[urtrivedi]

You are facing the problem due to your wrong design.
I will suggest you to change the design, otherwise you will end up with un managable system.

Subjects { subject_id (PK), subject_name & class_id (FK).}
Marks { mark_id, student_id(FK), exam_id (FK), subject_id(fk), marks_obtained}

[nadnakinam]

I design bu thinking this, (May be wrong)
Each classes(class_id) has its own subjects; some classes(class_id) can have common & unique subjects too. table2 namely marks table will have all the unique subjects as columns. Query 2 will get the values of the columns only that returned by the Query 1. for other columns it'll be default value NULL.

If it was Marks { mark_id, student_id(FK), exam_id (FK), subject_id(fk), marks_obtained} then each student have 10 entries (assuem 10 subjects)then for 50 students 10 clases it'll be 50*10*10 which reduces the performance while SEARCH.

Is there any better way then way i suggested? Please direct me..

Thanks

[urtrivedi]

Database will handle the performance in better manner doesnot matter how many records it have.

If you say your design is right, then tell me how u relate subject table with marks,
I guess u are storing marks obtain in sub1, sub2, so where are you storing subject id

Marks has mark_id, student_id(FK), exam_id (FK), sub_1, sub_2, ... sub_n

Even if we use your design, then you need to add one more column.

Marks has mark_id, student_id(FK), exam_id (FK), subid_1, submarks_1, subid_2, submarks_2, ... subid_n, submarks_n

The query you are trying to achieve is impossible and non standard. Please understand due to your design you struck badly.

[nadnakinam]

Hi urtrivedi,

you & my friends suggested a better idea(both u suggested same) than what i taught (but it'll sure increase the record size drastically)

Marks has mark_id, exam_id (FK), student_id(FK), subject_id(FK), mark

Thanks