Not able to make a query that give me the result that I want in MySQL with 4 tables junction
What you really want to be able to do is join the three tables with both a SKU and a location using a FULL JOIN; this joins rows where the indicated columns match, and also includes rows from both tables where the indicated fields do not match.

Unfortunately, MySQL 5.6 doesn't implement FULL JOIN. The standard workaround is to do something like this:

SELECT *
  FROM table1 t1
         LEFT  JOIN table 2 t2 ON t1.id = t2.id
UNION ALL
SELECT *
  FROM table1 t1
         RIGHT JOIN table 2 t2 ON t1.id = t2.id
 WHERE t1.id IS NULL
;


This gives you the results of the LEFT JOIN of the tables, and then unions in the results of the RIGHT JOIN of the tables, excluding the rows that would have been in the LEFT JOIN results.

The exclusion deals with the fact that both the LEFT JOIN and the RIGHT JOIN will return whatever rows an INNER JOIN would return. We want those rows - but we don't want to include each of them twice!

So, I built this query in three steps:


First, I built the query to return the rows from ventes and produitssite.
Then, I used that query as a subquery (identified as s1, for "step 1"), and joined its results to stocksminmax.
Finally, I used the second query as a subquery (identified as s2, for "step 2"), and LEFT JOINed those results to produits. For this final step, since it looks like you don't want to list and products that don't have a match in at least one of the three other tables, we don't have to do both a LEFT JOIN and a RIGHT JOIN.


Here's the query I got:

SELECT `sku`,`description`
      ,`smmsku`,`smmloc`,`smmphy`
      ,`pssku`,`psloc`,`psabcd`
      ,`ventesku`,`ventelocalisation`,`vente1805`
  FROM (
        SELECT `theSku`
              ,`theLoc`
              ,`smmsku`,`smmloc`,`smmphy`
              ,`pssku`,`psloc`,`psabcd`
              ,`ventesku`,`ventelocalisation`,`vente1805`
          FROM (SELECT `pssku` as theSku
                      ,`psloc` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         LEFT  JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                UNION ALL
                SELECT `ventesku` as theSku
                      ,`ventelocalisation` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         RIGHT JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                 WHERE ps.pssku IS NULL
               ) s1
                 LEFT  JOIN stocksminmax smm ON s1.theSku = smm.smmsku
                                            AND s1.theLoc = smm.smmloc
        UNION ALL
        SELECT `smmsku` as theSku
              ,`smmloc` as theLoc
              ,`smmsku`,`smmloc`,`smmphy`
              ,`pssku`,`psloc`,`psabcd`
                ,`ventesku`,`ventelocalisation`,`vente1805`
          FROM (SELECT `pssku` as theSku
                      ,`psloc` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         LEFT  JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                UNION ALL
                SELECT `ventesku` as theSku
                      ,`ventelocalisation` as theLoc
                      ,`pssku`,`psloc`,`psabcd`
                      ,`ventesku`,`ventelocalisation`,`vente1805`
                  FROM produitssite ps
                         RIGHT JOIN ventes v ON ps.pssku = v.ventesku
                                            AND ps.psloc = v.ventelocalisation
                 WHERE ps.pssku IS NULL
               ) s1
                 RIGHT JOIN stocksminmax smm ON s1.theSku = smm.smmsku
                                            AND s1.theLoc = smm.smmloc
         WHERE s1.theSku IS NULL
       ) s2
         LEFT  JOIN produits p ON s2.theSku = p.sku
;


I believe the results match what you expected - you can see them (and the query, working) in this db-fiddle.