CODEWITHSUNDEEP
mysql
Ajay
Ajay

Reputation: 2080

Adding columns while inserting a row in another table

my tables are

table1 is

         class_name    subject_Name

              I class         telugu
              I class          hindi
              II class         telugu
              II class          hindi

table2 is

         exam_name    telugu   hindi

            unit 1      25      35
            unit 2      30      35

Now i insert a row in table1 with subject maths.That subject has added to the table2 as column and also check whether the subject(maths) is present in table2.

my required output is

table1 is

         class_name    subject_Name

              I class         telugu
              I class          hindi
              II class         telugu
              II class          hindi
              II class         maths
              III class          hindi
              III class         telugu
              III class          maths

table2 is

         exam_name    telugu   hindi  maths

            unit 1      25      35     35
            unit 2      30      35      25

Thanks in advance....

Upvotes: 0

Views: 197

Answers (1)

Ursula
Ursula

Reputation: 412

I would restructure table 2 to normalize it, using these columns:

exam_name, subject, score

using the primary key exam_name, subject

You would then be able to query it to get all scores for the different subjects.

Table:

mysql> SELECT * FROM t2;
+-----------+---------+-------+
| exam_name | subject | score |
+-----------+---------+-------+
| unit1     | hindi   |    25 |
| unit1     | telugu  |    45 |
| unit2     | math    |    15 |
| unit2     | telugu  |    25 |
+-----------+---------+-------+

You can now query: First you need to find out all your subjects:

SELECT DISTINCT subject from t2;

Now you can use the subjects to create a pivot table:

SELECT exam_name, details.hindi, details.telugu, details.math 
FROM (
   SELECT exam_name, 
          SUM(if(subject='hindi',score,0)) AS hindi, 
          SUM(if(subject='telugu', score, 0)) AS telugu, 
          SUM(if(subject='math', score, 0)) AS math 
   FROM t2 GROUP BY exam_name

) AS details ;

+-----------+-------+--------+------+
| exam_name | hindi | telugu | math |
+-----------+-------+--------+------+
| unit1     |    25 |     45 |    0 |
| unit2     |     0 |     25 |   15 |
+-----------+-------+--------+------+

Look up pivot table for more detailed information. This solution assumes that there is only one entry for each exam_name/subject combination in the table.

You might be able to combined the two steps in one complex query. Depending on whether you call this from a script, that might or might not be preferable.

Upvotes: 2

Related Questions