mysqljoinleft-joindatabase-view

MySQL join not behaving as expected

I have created a MySQL view to grab data from four differents tables :

CREATE VIEW `documents_scannes_view` AS 
select `ds`.`id_document` AS `id_document`,
       `ba`.`num_boite` AS `num_boite`,
       `ds`.`annee` AS `annee`,
       `ds`.`ref_document` AS `ref_document`,
       `ds`.`num_client` AS `num_client`,
       `cl`.`nom_client` AS `nom_client`,
       `ds`.`nbre_a4` AS `nbre_a4`,
       `ds`.`nbre_a3` AS `nbre_a3`,
       `ds`.`nbre_autres` AS `nbre_autres`,
       ((`ds`.`nbre_autres` + `ds`.`nbre_a3`) + `ds`.`nbre_a4`) AS `nbre_total`,
       `ds`.`date_scan` AS `date_scan`,`ds`.`qualite` AS `qualite`,
       `ds`.`id_operateur` AS `id_operateur`,
       `ut`.`nom_complet` AS `nom_complet`,
       `ds`.`observations` AS `observations`,
       `ds`.`chemin` AS `chemin` 
from (((`boite_archive` `ba` left join `documents_scannes` `ds` on((`ds`.`num_boite` = `ba`.`num_boite`))) 
                             left join `clients` `cl` on((`ds`.`num_client` = `cl`.`num_client`))) 
                             left join `utilisateurs` `ut` on((`ds`.`id_operateur` = `ut`.`id_user`)))

The tables are respectively :

The joins is performed on the primary keys of the tables.

My objective is to get the list of the scanned documents (documents_scannes) from each box (boite_archive) fetching in the same time the name of the client (from clients table) and the name of the operator who has done the scan (from table utilisateurs).

My problem is when I :

SELECT * FROM documents_scannes_view WHERE num_boite = '1131';

The box number 1131 is empty it contains no scanned documents but I get one row :

enter image description here

Who can help me point the source of my error and how I can correct it ? Thanks in advance.

I am not sure about the joins in the bottom of the query. It's the source of the issue. How I can fix it to get no results when the box is emprt ?

Solution

  • If you don't want select an empty row where the join columns don't match change to an inner join instead of left join 

    from (((`boite_archive` `ba` inner join `documents_scannes` `ds` on((`ds`.`num_boite` = `ba`.`num_boite`))) 
                         left join `clients` `cl` on((`ds`.`num_client` = `cl`.`num_client`))) 
                         left join `utilisateurs` `ut` on((`ds`.`id_operateur` = `ut`.`id_user`)))

    The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

    enter image description here

    Image ref.