Problem with join in sql

Hello everyone !

I am having trouble with my join request and are asking for your help.
Indeed, I have two tables:


tb_user_notifications
NumId   | Date   | Sujet         | Message 
----------------------------------------------
1       | NULL   | Sujet 1       | Message 1
2       | NULL   | Sujet 2       | Message 2
3       | NULL   | Sujet 3       | Message 3
4       | NULL   | Sujet 4       | Message 4
5       | NULL   | Sujet 5       | Message 5
6       | NULL   | Sujet 6       | Message 6


tb_user_notifications_recap
NumId   | NIC_Handle   | Notification
----------------------------------------------
1       | JS38579      | 4
2       | JS38579      | 2
3       | JA90717      | 5
4       | JA90717      | 3
5       | TR22377      | 3
6       | TR22377      | 4
7       | TR22377      | 5
8       | TR22377      | 6
9       | TR22377      | 2
10      | TR22377      | 1
11      | JS38579      | 1
12      | JS38579      | 6

Open in new window


With this query, I would like to display in my table, all the notifications of tb_user_notifications considering that:
If the NIC_Handle corresponding to the username session ($_SESSION_NICKHANDLE) exists in the tb_user_notifications_recap table, then the button displays "fa fa-check txt_vert", otherwise it displays "fa fa-eye txt_bleu"
My example below only shows only "fa fa-check txt_vert" and omits unread notifications.


<table class="table table-striped table-hover table-bordered table-sm" id="Tableau_1-">
  <thead class="thead-dark">
    <tr>
      <th align="center" width="120">Date</th>
      <th><?php echo $MES_NOTIFICATIONS; ?></th>
      <th align="center" width="50"><i class="fa fa-comments"></i></th>
    </tr>
  </thead>
  <tbody>

<?php
try{$MyPDO=$pdo->query("
SELECT 			R.NIC_Handle, 
				R.Notification, 
				
				N.NumId, 
				N.Date, 
				N.Sujet, 
				N.Message

FROM 			tb_user_notifications N

LEFT JOIN 		tb_user_notifications_recap R ON N.NumId = R.Notification 

WHERE 			R.NIC_Handle = '".$_SESSION_NICHANDLE."' OR R.NIC_Handle IS NULL

ORDER BY 		N.Date DESC
"); while($data=$MyPDO->fetch(PDO::FETCH_ASSOC)){
?>

<?php if($data["Notification"] == $data["NumId"] AND $data["NIC_Handle"] == $_SESSION_NICHANDLE ) {$ICO = "fa fa-check txt_vert";} else {$ICO = "fa fa-eye txt_bleu";} ?>

    <tr>
      <td><div style="padding:5px"><?php echo date("$DATE_SQL", strtotime($data["Date"])); ?></div></td>
      <td><div style="padding:5px"><?php echo $data["Sujet"]; ?></div></td>
      <td align="center"><button class="border btn btn-light" onClick="location.href='/my/notifications-view.php?page=<?php echo $data["NumId"]; ?>';" ><i class="<?php echo $ICO; ?>"></i></button></td></tr>

<?php }}catch(PDOException $e){echo "<div class='alert alert-danger'>".$e->getMessage()."</div>";} ?>

  </tbody>
</table>

Open in new window


In this exemple, my username is ($_SESSION_NICKHANDLE) is JA90717
This is what I see in my webpage :
Sujet 5
Sujet 3

Dans for the username JS38579, he see :
Sujet 6
Sujet 4
Sujet 2
Sujet 1

Can you please help me solve this problem?
Thank you very much !
Jaber
Ahmad JaberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Your query is filtering on the $_SESSION_NICHANDLE value, so your IF statement will always return true (you're only selecting records that match $_SESSION_NICHANDLE). You don't need to be filtering the query - just select all records, and then run your IF. There's also no need to check if NumId and Notification match - they will - the records are joined on those columns. It's probably also cleaner to do your IF check using the ternary operator:

<?php $ICO = ( $data["NIC_Handle"] == $_SESSION_NICHANDLE ) ? "fa fa-check txt_vert" : "fa fa-eye txt_bleu"; ?> 

Open in new window

Ahmad JaberResponsableCommented:
Hello and thank you for taking care of my problem.
If I understand correctly, a standard query will solve the problem, but when I do it, I still do not have the desired result.

I change in the LEFT JOIN, I made a GROUP BY, I try everything I know and can do but to no avail, still no satisfactory result.

If I select everything from tb_user_notification_recap (NumId, NIC_Handle, Notification), I would not have the subject to display it in the table and would still have to make a WHERE NIC_Handle = $_SESSION_NICHANDLE in order to display only the notifications read.

I do not know where I put my head!

Here is what I must have as a result
result.png
Chris StanyonWebDevCommented:
Ahh right.

In that case, you need to join to a subquery:

SELECT R.NIC_Handle, R.Notification, N.NumId, N.Date, N.Sujet, N.Message
FROM tb_user_notifications N
LEFT JOIN
(SELECT NIC_Handle, Notification FROM tb_user_notifications_recap WHERE NIC_Handle = 'JA90717') R
ON N.NumId = R.Notification
ORDER BY N.Date DESC;

Open in new window

And then just check for NULL in the Notification column

$ICO = ( $data["Notification"] == null ) ? "fa fa-eye txt_bleu" : "fa fa-check txt_vert";

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ahmad JaberResponsableCommented:
Seriously, even in dreams I would not have found!
Thank you very much for your help!

The problem was resolved !
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.