Requête sur deux table MySQL

Question

Bonjour SVP comment obtenir le résultat ci-dessous

Voilà la requête que j'ai fait mais ça ne marche pas:

SELECT entrees.code_article, entrees.designation, sum(entrees.quantite), sorties.code_article, sum(sorties.quantite) from entrees, sorties where entrees.code_article=sorties.code_article

infoclick · Answer

Urgent SVP

jordane45 · Answer

Avec une structure de table plus adéquat (et encore.. on peut l'améliorer... )

-- --------------------------------------------------------
-- Hôte:                         127.0.0.1
-- Version du serveur:           5.6.12-log - MySQL Community Server (GPL)
-- Serveur OS:                   Win32
-- HeidiSQL Version:             9.1.0.4867
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

-- Export de la structure de table magasin. article
CREATE TABLE IF NOT EXISTS `article` (
  `code` bigint(20) NOT NULL AUTO_INCREMENT,
  `id_categorie` bigint(20) NOT NULL,
  `designation` varchar(50) NOT NULL,
  `description` text,
  PRIMARY KEY (`code`),
  KEY `id_categorie` (`id_categorie`)
) ENGINE=InnoDB AUTO_INCREMENT=7892 DEFAULT CHARSET=utf8;

-- Export de données de la table magasin.article: ~0 rows (environ)
DELETE FROM `article`;
/*!40000 ALTER TABLE `article` DISABLE KEYS */;
INSERT INTO `article` (`code`, `id_categorie`, `designation`, `description`) VALUES
 (1234, 1, 'produit 1', NULL),
 (7891, 1, 'produit 2', NULL);
/*!40000 ALTER TABLE `article` ENABLE KEYS */;

-- Export de la structure de table magasin. categorie
CREATE TABLE IF NOT EXISTS `categorie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `libelle` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Export de données de la table magasin.categorie: ~0 rows (environ)
DELETE FROM `categorie`;
/*!40000 ALTER TABLE `categorie` DISABLE KEYS */;
INSERT INTO `categorie` (`id`, `libelle`) VALUES
 (1, 'Fourniture informatique');
/*!40000 ALTER TABLE `categorie` ENABLE KEYS */;

-- Export de la structure de table magasin. entree
CREATE TABLE IF NOT EXISTS `entree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code_article` bigint(20) DEFAULT NULL,
  `qte` float DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `code_article` (`code_article`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- Export de données de la table magasin.entree: ~4 rows (environ)
DELETE FROM `entree`;
/*!40000 ALTER TABLE `entree` DISABLE KEYS */;
INSERT INTO `entree` (`id`, `code_article`, `qte`, `date`) VALUES
 (1, 1234, 10, '2018-05-16 19:13:00'),
 (2, 1234, 30, '2018-12-10 00:00:00'),
 (3, 1234, 20, '2019-01-16 19:13:00'),
 (4, 7891, 50, '2019-01-16 19:13:33');
/*!40000 ALTER TABLE `entree` ENABLE KEYS */;

-- Export de la structure de table magasin. sortie
CREATE TABLE IF NOT EXISTS `sortie` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code_article` bigint(20) DEFAULT NULL,
  `qte` float DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `code_article` (`code_article`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

-- Export de données de la table magasin.sortie: ~1 rows (environ)
DELETE FROM `sortie`;
/*!40000 ALTER TABLE `sortie` DISABLE KEYS */;
INSERT INTO `sortie` (`id`, `code_article`, `qte`, `date`) VALUES
 (1, 1234, 40, '2019-01-12 19:13:54');
/*!40000 ALTER TABLE `sortie` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

La requête pour obtenir ce que tu souhaites faire ressemble à

SELECT A.*
      ,C.libelle
      ,SOR.S
      ,ENT.E
      ,coalesce(ENT.E,0) - coalesce(SOR.S,0) as QUANTITE
FROM article A
LEFT JOIN categorie C ON C.id = A.id_categorie
LEFT JOIN (SELECT code_article, SUM(qte) as S FROM sortie GROUP BY code_article ) SOR ON SOR.code_article = A.code
LEFT JOIN (SELECT code_article, SUM(qte) as E FROM entree GROUP BY code_article ) ENT ON ENT.code_article = A.code

Requête sur deux table MySQL

2 réponses

Discussions similaires

Newsletters