how to use union resolve this full outer join problem under mySQL

Here is the table

 stuid stuname subject grade
 1     alex    algo    99
 1     alex    dastr   100
 2     bob     algo    90
 2     bob     dastr   95
 3     casy    algo    100
 4     Daisy   dastr   100

case1: assuming there are only two subjects in the table Following is the expected output

 stuname algo dastr 
 alex    99    100
 bob     90    95
 casy    100   0
 Daisy   0     100

I think following is a workable query

select g1.stuname,
       COALESCE(g1.grade,0) as algo
       COALESCE(g2.grade,0) as dastr
from grades g1 
full outer join grades g2 on g1.stuid = g2.stuid
where g1.subject = algo and g2.subject = dastr;

But, mysql doesnt support full outer join. Is there any other way to resolve the problem?

Also, case 2 assuming there are unknown number of subjects in the table and the expected output would be

stuname subj1 subj2 subj3 ... subjn

I know I might be using procedure resolve it, is there any other way that I can use to compose col开发者_如何学编程umns in mySQL?

---

Your queries would work better if you re-structured your tables. You are attempting to store too much information in one table. Here is a proposed structure:

Students
student_id student_name
1          Alex
2          Bob
3          Casy
4          Daisy

Subjects
subject_id subject_name
1          Algo
2          Dastr

Grades
student_id subject_id grade
1          1          99
1          2          100
2          1          90
2          2          95
3          1          100
4          2          100

In grades, student_id and subject_id would be a composite key, meaning a unique combination of the two becomes the unique identifier (student 1, subject 1 is unique from student 1, subject 2)

To return the data based on your comment, try:

  SELECT a.student_name, b.subject_name, c.grade
    FROM students a, subjects b, grades c
   WHERE a.student_id = c.student_id
     AND b.subject_id = c.subject_id
ORDER BY a.student_id

---

Have you tried something along the line of:

SELECT a.stuid as sidA, a.grade as grA, a.grade as grB
FROM grades a JOIN grades b ON (a.stuname = b.stuname)

But as D.N. suggested, it may be worth restructuring your tables

---

From your existing data...

select 
      stuid,
      max( stuName ) stuName, 
      max( if( subject = "algo", grade, 000 )) as Algo,
      max( if( subject = "dastr", grade, 000 )) as Dastr
   from 
      Grades
   group by
      stuid
   order by
      stuName

However, if you have multiple people with the same "StuName", by grouping by their unique ID, it will keep them differentiated, so for clarification, I've included the ID column in the final query.

However, the data restructuring as suggested by @D.N. would be a cleaner approach.