mysql - query with several LEFT JOIN , not getting desired result -

here's sql fiddle

can tell me how can output using left join?

 notification_recipient  pm_sender    msg     modification_page_id   peter                   tom          hello   null  notification_recipient  pm_sender    msg     modification_page_id   peter                   null         null    2 

here's query have tried:

   select u.name notification_recipient,us.name pm_sender,           p.msg,um.page_id modification_page_id      notification n      left join pm p on p.pm_id = n.pm_id     left join users u on u.user_id = p.recipent_id     left join users on us.user_id = p.sender_id     left join user_modification um on um.modification_id = n.modification_id     u.name = 'peter'     , n.is_read = '0'   

i looking sort conditional join, means joining different tables based on whether value exists in field, couldn't find 1 work on example. other efficient solution appreciated.

background:

i'm planning make notification system sends out different types of messages users (private messages among users, message modifications entry has been approved etc). when user logs in, want make query find out if there's unread notifications user. if yes, notification sent him via ajax.

to illustrate, suppose tom has sent private message peter , modification entry approved, 2 triggers table pm , user_modification invoked add 2 new rows notification. column pm_id referenced pm , modification_id modification. is_read defaulted 0 not read.

here's table schema:

create table notification     (`id` int, `modification_id` int,`pm_id` int,`is_read` int) ;  insert notification     (`id`,`modification_id`,`pm_id`,`is_read`) values     (1,1,null,0),     (2,null,1,0),     (3,2,null,0) ;  create table user_modification     (`modification_id` int, `user_id` int,`page_id` int, `is_approved` int) ;  insert user_modification     (`modification_id`,`user_id`,`page_id`,`is_approved`) values     (1,1,5,1),     (2,2,2,1),     (3,3,3,0)  ;  create table pm     (`pm_id` int, `sender_id` int,`recipent_id` int,`msg` varchar(200)) ;  insert pm     (`pm_id`,`sender_id`,`recipent_id`,`msg`) values     (1,1,2,'hello');  create table users     (`user_id` int, `name`varchar(20)) ;  insert users     (`user_id`,`name`) values     (1,'tom'),     (2,'peter'),     (3,'david') ; 

here's output want notification if user david logs in. each row each message type.

 notification_recipient  pm_sender    msg     modification_page_id   peter                   tom          hello   null  notification_recipient  pm_sender    msg     modification_page_id   peter                   null         null    2 

the notification peter this:

        `1.you have received private message tom.          2.your modification on <a href='mysite.com/5'>that page</a> approved`. 

this query should job. here's sqlfiddle

select   n.id,   if(pmu.name null, pmm.name, pmu.name) recipient,    pmus.name sender, pm.msg, m.modification_id   notification n   left join user_modification m on (n.modification_id = m.modification_id)   left join pm on (n.pm_id = pm.pm_id)   left join users pmu on (pm.recipent_id = pmu.user_id)   left join users pmus on (pm.sender_id = pmus.user_id)   left join users pmm on (m.user_id = pmm.user_id)   (pmu.name = 'peter' or       pmm.name = 'peter') ,   n.is_read = 0;