[SOLVED] Query 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

 

 

[revraz]

I don't think you should be using the level as a join, you should just display it

[Barand]

try LEFT JOIN instead of INNER JOIN

[arifsor]

left join is also not working?

 

any idea.

[Barand]

try

$q = "SELECT u.user_name, 
        SUM(IF(q.level=1, marks_obtained, 0)) AS level_one_marks, 
        SUM(IF(q.level=2, marks_obtained, 0)) AS level_two_marks , 
        SUM(IF(q.level=3, marks_obtained, 0)) AS level_three marks 
        FROM user u
        INNER JOIN qualification q ON u.id = q.user_id ";

[arifsor]

Thanks