1

I need help to adjust my query to be able to have the result show in bottom.

This is my DB illustration:

enter image description here

And this is the command CREATE TABLE for each table:

CREATE TABLE `produits` (
  `sku` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `produits` (`sku`, `description`) VALUES
('1', 'Produit 1'),
('2', 'Produit 2'),
('3', 'Produit 3');


CREATE TABLE `produitssite` (
   `pssku` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
   `psloc` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
   `psabcd` varchar(10) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `produitssite` (`pssku`, `psloc`, `psabcd`) VALUES
('1', 'A', 'd'),
('1', 'B', 'd'),
('2', 'A', 'c'),
('3', 'A', 'b'),
('4', 'C', 'd');


CREATE TABLE `stocksminmax` (
  `smmsku` varchar(250) CHARACTER SET latin1 NOT NULL,
  `smmloc` varchar(250) CHARACTER SET latin1 NOT NULL,
  `smmphy` float(20,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `stocksminmax` (`smmsku`, `smmloc`, `smmphy`) VALUES
('1', 'A', 10.00),
('1', 'B', 20.00),
('2', 'A', 20.00),
('3', 'B', 5.00),
('4', 'B', 10.00);


CREATE TABLE `ventes` (
  `ventesku` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `ventelocalisation` varchar(250) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `vente1805` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `ventes` (`ventesku`, `ventelocalisation`, `vente1805`) VALUES
('1', 'A', 500),
('1', 'B', 250),
('2', 'B', 600),
('3', 'A', 400),
('4', 'A', 100);

Here are the three requests that I try to obtain in 1:

SELECT * FROM produits RIGHT JOIN stocksminmax ON sku = smmsku

SELECT * FROM produits RIGHT JOIN produitssite ON sku = pssku

SELECT * FROM produits RIGHT JOIN ventes ON sku = ventesku

But I also try to JOIN the location on each table in my request : smmloc, psloc and venteloc

This is what I try to obtain: enter image description here

Thanks for your help.

0
1

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.

0
0

This is the most logical answer to your requirement:

SELECT `sku`, `description`,`smmsku`, `smmloc`, `smmphy`,`pssku`, `psloc`, `psabcd`,`ventesku`, `ventelocalisation`, `vente1805` FROM ventes
LEFT JOIN stocksminmax ON ventesku = smmsku AND smmloc = ventelocalisation
LEFT JOIN produitssite ON ventesku = pssku AND psloc = ventelocalisation
LEFT JOIN produits ON ventesku = sku

Not the answer you're looking for? Browse other questions tagged or ask your own question.