[SOLVED] SQL JOIN issue

[arifsor]

please help me related to the db sch.

 

user

 

id | user_name

---|----------------

1  |  Willium

2  |  John

--------------------

 

qualification

id | user_id | level | marks_obtained

---------------------------------------------

1 | 1            | 1      | 50

---------------------------------------------

2 | 1            | 2      | 80

----------------------------------------------

3 | 1            | 3      | 90

---------------------------------------------

4 | 2            | 1      | 50

---------------------------------------------

5 | 2            | 2      | 40

---------------------------------------------

 

Required out put report

 

user_name  | level_one_marks | level_two_marks  |  level_three_marks

-------------------------------------------------------------------------------------------

Willium        | 50                        | 80                        |        90

John            | 50                        | 40                        |        NULL

--------------------------------------------------------------------------------------------

 

* my query

select u.user_name, level_one.marks_obtained as level_one_marks, level_two.marks_obtained as level_two_marks , level_three.marks_obtained as level_three marks from user u

inner join qualification level_one on u.id = level_one.user_id

inner join qualification level_two on u.id = level_two.user_id

inner join qualification level_three on u.id = level_three.user_id

where level_one.level = 1 and level_tow.level = 2 and level_three.level = 3

 

but above query only fetch one row

 

user_name  | level_one_marks | level_two_marks  |  level_three_marks

-------------------------------------------------------------------------------------------

Willium        | 50                        | 80                        |        90

--------------------------------------------------------------------------------------------

 

becauze level three mark is not availbel in qualification table for the user id 2

how can i produce my required report? please help

 

 

[Gamic]

select 
  u.user_name, 
  level_one.marks_obtained as level_one_marks, 
  level_two.marks_obtained as level_two_marks , 
  level_three.marks_obtained as level_three marks 
from 
  user u left outer join 
  qualification level_one on u.id = level_one.user_id left outer join
  qualification level_two on u.id = level_two.user_id left outer join 
  qualification level_three on u.id = level_three.user_id
where level_one.level = 1 and level_tow.level = 2 and level_three.level = 3

[arifsor]

Thanks