Search code examples
mysqlsqlinner-joinunion

How to get multiple rows from single row of table?

Hi I have two tables with following structure

Students

 +------+------+------+-------+------+------+
| Col1 | Col2 | Col3 | Col4  | Col5 | Col6 |
+------+------+------+-------+------+------+
|   01 | Hari | 20   |    80 |   21 |   81 |
|   02 | Nari | 20   |    67 |   21 |   76 |
|   02 | Lari | 25   |    87 |   26 |   96 |
+------+------+------+-------+------+------+

Here COl1= ID, Col2= Name,Col3= SubjectCode, COl4= Marks in Col3, Col5= subjectCpde, Col6= Marks for Col5

And another table called subjects

+------+-----------+
| Col1 |   Col2    |
+------+-----------+
|   20 | English   |
|   21 | Maths     |
|   25 | Chemistry |
|   26 | Physics   |
+------+-----------+

Here Col1= Subject Code, COl2= Subject Name which were referenced on in student table.

Now my query should retturn the result as follows. How it is achieved?

+---------+--------+------+
| Subject |  Marks | Name |
+---------+--------+------+
| English |     80 | Hari |
| Maths   |     81 | Hari |
+---------+--------+------+
Solution

  • You can try this.

    Using two query, first query subjectCode by English ,second query subjectCode by English. then use UNION ALL to combine them.

    SELECT sub.Col2 as 'Subject',stu.Col4 as 'Marks',stu.Col2 as 'Name'
FROM Students stu
inner join subjects sub on sub.Col1 = stu.Col3
WHERE stu.Col2 = 'Hari'
UNION ALL 
SELECT sub.Col2 as 'Subject',stu.Col6 as 'Marks',stu.Col2 as 'Name'
FROM Students stu
inner join subjects sub on sub.Col1 = stu.Col5
WHERE stu.Col2 = 'Hari'

    http://sqlfiddle.com/#!9/513fe5/17