manianis manianis - 22 days ago 6
MySQL Question

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 :


  • documents_scannes : List of scanned documents

  • boite_archive : List of box that contains the scanned documents

  • clients : List of customer that issued the scanned documents

  • utilisateurs : List of personal that are scanning the documents



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 ?

Answer

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

Comments