Select data from multiple tables

Question

How can I join multiple rows in just one single row through mysql?

Example :

Student Table

Sno.|  Name |  Subjects
1.  | ABC   |  1
2.  | ABC   |  3
3.  | ABC   |  4
4.  | FMC   |  2
5.  | ABC   |  4
6.  | JBC   |  4

Papers Table:

Sno. | Paper Name | Type 
 1.      French     Optional
 2.      English    Mandatory
 3.      Japenese   Optional
 4.      Maths      Optional

Now I want it in this format

Sno.| Name|   Sub1  |  Sub2    | Sub3  |  Sub4 |
1.  | ABC |  French |  Japenese| Maths | Null  |
2.  | FMC |  Null   | Null     | Null  | Null  |
3.  | JBC |  Maths  | Null     | Null  | Null  |

What i want to select is papers name from papers table and student name, four subjects from the other table. I only want to see those paper which are optional. I am not sure what to do?

What have you tried? Have a look at mysql `JOIN` statement and come back if you fail understanding the usage with a more specific question. — favoretti, Oct 28 '12 at 12:36
I have tried Join query and few other queries as well. It didn't work. The problem is i have converted my vertical table into a horizontal one but i am not sure how to fetch paper name from the paper table where the type is optional. — I Perfect, Oct 28 '12 at 12:38
@favoretti The query i run to change it from vertical to horizontal is : SELECT t1.Name, MIN(t1.Subject) AS Sub1, MIN(t2.Subject) AS Sub2, MIN(t3.Subject) AS Sub3, MIN(t4.Subject) AS Sub4 FROM Students t1 LEFT JOIN Students T2 ON t1.Name = t2.Name AND t2.Subject > t1.Subject LEFT JOIN Students T3 ON t2.Name = t3.Name AND t3.Subject > t2.Subject LEFT JOIN Students T4 ON t3.Name = t4.Name AND t4.Subject > t3.Subject GROUP BY t1.Name; — I Perfect, Oct 28 '12 at 12:42
@IPerfect and you suggest to change query every time when subject table was edited? — ozahorulia, Oct 28 '12 at 12:45
@IPerfect, Hast is saying that you will have to add a few more line if, eg., you add Science (of optional Type) in the Paper Table. Things get much easier if you can output the results vertically. — amemus, Oct 28 '12 at 13:04
Here's some ideas based on your query: http://sqlfiddle.com/#!2/03479/7/0 — Laurence, Oct 28 '12 at 13:05
I think _Maths_ should be _Sub4_ for Student _ABC_ in your Example. — moj, Oct 28 '12 at 14:19
I think a better name for the question is: _"pivoting with multiple tables in msql"_ — moj, Oct 28 '12 at 15:23

moj · Accepted Answer · 2012-10-28T15:14:55.860

I think the concept you are looking for is pivoting.

Version with join

select 
  name,
  max(if(s.subject = 1 and p.type = 'Optional', p.paper_name, null)) as subject1,
  max(if(s.subject = 2 and p.type = 'Optional', p.paper_name, null)) as subject2,
  max(if(s.subject = 3 and p.type = 'Optional', p.paper_name, null)) as subject3,
  max(if(s.subject = 4 and p.type = 'Optional', p.paper_name, null)) as subject4
from 
  students s
inner join
  papers p on p.sno = s.subject
group by s.name

SQL Fiddle Demo

Version with subselects instead of a join

select 
  name,
  max(
    case 
      when subject = 1 
        then (select paper_name from papers p where p.sno = subject and type = 'Optional') 
      else 
        null 
    end) as subject1, 
  max(
    case 
      when subject = 2 
        then (select paper_name from papers p where p.sno = subject and type = 'Optional') 
      else 
        null 
    end) as subject2,
  max(
    case 
      when subject = 3 
        then (select paper_name from papers p where p.sno = subject and type = 'Optional') 
      else 
        null 
    end) as subject3,
  max(
    case 
      when subject = 4
        then (select paper_name from papers p where p.sno = subject and type = 'Optional') 
      else 
        null 
    end) as subject4
from 
  students
group by 
  name

SQL Fiddle Demo

Another question about pivoting: http://stackoverflow.com/questions/7674786/mysql-pivot-table — moj, Oct 28 '12 at 14:55

Select data from multiple tables

1 Answers1

Version with join

Version with subselects instead of a join