Query with multiple LEFT JOINs without getting the desired result

Here's the sql script

Can anyone tell me how can I get this 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 is the query I tried:

   SELECT u.name AS notification_recipient,us.name AS pm_sender,
          p.msg,um.page_id AS modification_page_id 
    FROM 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 us ON us.user_id = p.sender_id
    LEFT JOIN user_modification um ON um.modification_id = n.modification_id
    WHERE u.name = 'Peter'
    AND n.is_read = '0'  

      

        
        
        
      

    

I was looking for a conditional join of a specific type, which means joining different tables based on whether a value exists in the field, but I couldn't find one that would work for my example. Any other effective solution would be appreciated as well.

Background:

I am planning to create a notification system that sends users different types of messages (personal messages among users, a message that their changes to a record have been approved, etc.). When a user logs in, I want to make a query to see if there are any unread notifications for that user. If so, the notification will be sent to him via Ajax.

To illustrate, suppose Tom sends a personal message to Peter and the modification is approved for approval, two triggers from the table will be invoked pm

and user_modification

to add two new rows to notification

. The column pm_id

refers to pm

and modification_id

to modification

. is_read

by default it is considered 0

Not Read.

Here's the table schema:

CREATE TABLE notification
    (`id` int, `modification_id` int,`pm_id` int,`is_read` int)
;

INSERT INTO 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 INTO 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 INTO pm
    (`pm_id`,`sender_id`,`recipent_id`,`msg`)
VALUES
    (1,1,2,'Hello');

CREATE TABLE users
    (`user_id` int, `name`varchar(20))
;

INSERT INTO users
    (`user_id`,`name`)
VALUES
    (1,'Tom'),
    (2,'Peter'),
    (3,'David')
;

      

        
        
        
      

    

Here's the output I want to be notified if user David logs in. Each line for each type of message.

 notification_recipient  pm_sender    msg     modification_page_id 
 Peter                   Tom          Hello   NULL
 notification_recipient  pm_sender    msg     modification_page_id 
 Peter                   NULL         NULL    2

      

        
        
        
      

    

Peter's notice will be like this:

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