MySQL join not behaving as expected

Question

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 :

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 ?

It's 1130 is a typo error the empty box is 1131. I corrected the mistyped number. — manianis, Nov 11 '16 at 16:29
It's not the '' around the number that causes the error, because it's working with others boxes numbers. — manianis, Nov 11 '16 at 16:32
Then I don't see what's wrong. The engine is doing what you want. It's returning the 1 record in boite_archive that matches 1131; and since no corresponding records match in other tables, all other fields are null. Meaning specifically, look at boite_archive for just 1131 1 record right? now look at documents_scannes only for 1131. No record right? missing record there causes all other values to be null! — xQbert, Nov 11 '16 at 16:32
Isn't weired to get a result when there's no documents in the box ? — manianis, Nov 11 '16 at 16:34
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 ? — manianis, Nov 11 '16 at 16:38
Just so I'm clear which field is "Box" @scaisEdge had an answer that may have worked simply by changing the first left join to an inner join. so that if no documetns_scannes existed for the num_boite in boite_archive, they would be excluded. ...`from (((boite_archive ba INNER join documents_scannes ds`... though ask yourself why do you have a boite_archive without a documents_scannes record? Are you sure you want to omit those? — xQbert, Nov 11 '16 at 16:39
Thanks, that's worked fine by replacing the first left join by an inner join as you said. Would I delete or keep my question that it's resolved ? — manianis, Nov 11 '16 at 16:45
Really I'd like @scaisEdge to undelete their answer. as it really was what you needed. I just thought the problem was the 1311 vs the 1130. (while extremely odd It would have been fun to try and fix :P) Give him a day and see if he undeletes it if so accept his otherwise withdraw/delete the question in my book. — xQbert, Nov 11 '16 at 16:45
@xQbert, Yes it's normal to have an empty archive box before any document have been scanned and inserted inside it. — manianis, Nov 11 '16 at 16:47
You have a question that references a data set, but you tell us nothing about that data set. :-( — Strawberry, Nov 11 '16 at 16:49
@manianis there you go scaisEdge seems like the correct response! — xQbert, Nov 11 '16 at 16:50

score 2 · Accepted Answer · edited Nov 11 '16 at 17:04

2

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.

Image ref.

edited Nov 11 '16 at 17:04

Stacked

6,892
7
57
73

answered Nov 11 '16 at 15:36

ScaisEdge

131,976
10
91
107

By doing some search I have found that : 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. – manianis Nov 11 '16 at 16:54
1

One of my personal favorites for explaining the basic joins: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ I like the picutre and updates too! – xQbert Nov 11 '16 at 17:00

MySQL join not behaving as expected

1 Answers1