Mysql – Little trouble with MySQL LEFT JOIN

MySQL

I ask your help on a subject that I do not really master.

I have two tables:
tb_user (NIC_Handle, Pseudo)
tb_user_messages_recap (Auteur, Intervenant)

I have to retrieve the NIC_Handle of each 'Pseudo' that corresponds to either 'Auteur' or 'Intervenant'

Can you help me please ?

<?php try{
$MyPDO=$pdo->query("
SELECT 
tb_user_messages_recap.Auteur,
tb_user_messages_recap.Intervenant,
tb_user_messages_recap.Numero,
tb_user_messages_recap.Sujet,
tb_user_messages_recap.Creation,
tb_user_messages_recap.Edition,
tb_user_messages_recap.Etat,
tb_user.NIC_Handle,
tb_user.Pseudo

FROM tb_user_messages_recap

LEFT JOIN tb_user

ON tb_user_messages_recap.Auteur = tb_user.Pseudo

WHERE tb_user_messages_recap.Auteur='".$_SESSION_PSEUDO."' OR 
      tb_user_messages_recap.Intervenant='".$_SESSION_PSEUDO."' 

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

<?php echo $data["NIC_Handle"]." - ".$data["Auteur"]; ?>
<?php echo $data["NIC_Handle"]." - ".$data["Intervenant"]; ?>
<?php echo $data["NIC_Handle"]." - ".$data["Intervenant"]; ?>

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

Thank you for your help,

Cordialy

Best Answer

Change the join condition to:

ON tb_user_messages_recap.Auteur = tb_user.Pseudo OR
   tb_user_messages_recap.Intervenant= tb_user.Pseudo

But really why not:

SELECT NIC_Handle
FROM tb_user
WHERE Pseudo =$_SESSION_PSEUDO

I could be wrong. Question is a bit ambiguous.

Related Solutions

MYSQL joining 3 tables with left join

I suggest you first read this question and answer Help with this query.

Then try to rewrite your queries using Option 3 (or all of the options). The following is a first attempt at option 1:

SELECT 
    p.id_pupil
  , p.name
  , p.surname
  , ROUND(AVG(q.calificacion),2) AS average
  , COUNT(DISTINCT q.id_qualification) AS count
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss'        THEN i.id_incidents END)
       AS misses
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'delay'       THEN i.id_incidents END)
       AS delays
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'attitude'    THEN i.id_incidents END)
       AS attitude
  , COUNT(DISTINCT CASE WHEN i.type_incident = 'miss_justif' THEN i.id_incidents END)
       AS misses_justificadas
FROM 
    pupils                   AS p 
    LEFT JOIN incidents      AS i  ON  i.id_pupil = p.id_pupil
                                   AND i.id_trimester = 1
    LEFT JOIN qualifications AS q  ON  q.id_pupil = p.id_pupil
                                   AND q.id_trimester = 1
                                   AND q.type_qualification = 'class' 
WHERE 
    p.level = 1
    AND p.class = 'A' 
    AND p.id_trimester = 1 
GROUP BY 
    p.id_pupil 
  , p.name
  , p.surname ;

MySQL select with left join query

I updated class_teachers table to class_teachers_section to include section. The idea is a class can include different sections with different teachers. For example a grade 1 PE class can include all sections; the teacher for boys will be different from that for girls.

select class.name as classname, 
   class.hours as hours, 
   teachers.first_name as teacherFirstName, 
   teachers.last_name as teacherLastName,
   section.name as sectionName
 from class_teachers_section cls 
 left join class on class.id = cls.class_ref  
 left join teachers on teachers.id = cls.teachers_ref
 left join section on section.id = cls.section_ref
 where section.name = 'grade 1'

Related Question