query left join error

All we need is an easy explanation of the problem, so here it is.

I have two queries that get me a correct result, but when I try to join they give me an incorrect result.

I want to make something like:

SELECT  directoscount.parte, 
        SUM(directoscount.cantidad), 
        lx02.material, 
        SUM(lx02.totalStock) 
FROM directoscount 
    LEFT JOIN lx02 
        ON directoscount.parte = lx02.material 
WHERE directoscount.deleted_at IS null 
AND            lx02.deleted_at IS null 
GROUP BY directoscount.parte, lx02.material

I make a db fiddle to represent what I want and what I get.

Tables and Data

CREATE TABLE `directoscount` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `reg` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parte` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ubiclinea` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tramo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `brazo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `codigo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `uso` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `awcm` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ruta` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numtarjetas` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `surtido` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parada` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `familia` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `plataforma` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `auditoria` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descripcion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `numConteo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cantidad` int(255) DEFAULT NULL,
  `counter` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ifa` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `directoscount`
--

INSERT INTO `directoscount` (`id`, `reg`, `parte`, `ubiclinea`, `tramo`, `brazo`, `codigo`, `uso`, `awcm`, `ruta`, `numtarjetas`, `surtido`, `parada`, `familia`, `plataforma`, `auditoria`, `descripcion`, `numConteo`, `cantidad`, `counter`, `ifa`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, '106500', '486298', 'LEC 060F', 'CONTROL BOX', 'C', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .750 TR CR A', '1', 12, 'Cesar', 'IFA 2021', '2021-08-09 19:07:05', '2021-08-09 19:07:26', '2021-08-09 19:07:26'),
(2, '106500', '486298', 'LEC 060F', 'CONTROL BOX', 'C', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .750 TR CR A', '2', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:07:26', '2021-08-09 19:07:26', NULL),
(3, '107147', '486298', 'LED 055F', 'EVAPORADOR', 'D', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '1', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:09:38', '2021-08-09 19:09:53', '2021-08-09 19:09:53'),
(4, '107147', '486298', 'LED 055F', 'EVAPORADOR', 'D', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '2', 23, 'Cesar', 'IFA 2021', '2021-08-09 19:09:53', '2021-08-09 19:14:06', '2021-08-09 19:14:06'),
(5, '107147', '486298', 'LED 055F', 'EVAPORADOR', 'D', 'T98(2)', 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '3', 23, 'Cesar', 'IFA 2021', '2021-08-09 19:14:06', '2021-08-09 19:14:06', NULL),
(6, '109458', '486298', 'LEA 080F', 'CRISPERS', 'A', 'T98(2)', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .750 TR CR A', '1', 11, 'Cesar', 'IFA 2021', '2021-08-09 19:17:26', '2021-08-09 19:17:26', NULL),
(7, '106506', '488208', 'LEC 070F', 'CONTROL BOX', 'C', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .813 HW A', '1', 20, 'Cesar', 'IFA 2021', '2021-08-09 19:18:02', '2021-08-09 19:19:02', '2021-08-09 19:19:02'),
(8, '106506', '488208', 'LEC 070F', 'CONTROL BOX', 'C', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', '5', NULL, NULL, 'A', '8-15 X .813 HW A', '2', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:19:02', '2021-08-09 19:19:02', NULL),
(9, '107150', '488208', 'LED 070F', 'CONTROL BOX', 'D', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '1', 11, 'Cesar', 'IFA 2021', '2021-08-09 19:19:51', '2021-08-09 19:21:50', '2021-08-09 19:21:50'),
(10, '107150', '488208', 'LED 070F', 'CONTROL BOX', 'D', NULL, 'XCL JUNO', 'JN- ASY- FRONT', '2J', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '2', 11, 'Cesar', 'IFA 2021', '2021-08-09 19:21:50', '2021-08-09 19:21:50', NULL),
(11, '109471', '488208', 'LEA 090F', 'CRISPERS', 'A', '#N/A', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '1', 22, 'Cesar', 'IFA 2021', '2021-08-09 19:22:13', '2021-08-09 19:22:28', '2021-08-09 19:22:28'),
(12, '109471', '488208', 'LEA 090F', 'CRISPERS', 'A', '#N/A', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '2', 44, 'Cesar', 'IFA 2021', '2021-08-09 19:22:28', '2021-08-09 19:23:12', '2021-08-09 19:23:12'),
(13, '109471', '488208', 'LEA 090F', 'CRISPERS', 'A', '#N/A', 'COMUN', 'ZS- ASY- FRONT', '2Z', '1', 'SUPERMERCADO', NULL, NULL, NULL, 'A', '8-15 X .813 HW A', '3', 44, 'Cesar', 'IFA 2021', '2021-08-09 19:23:12', '2021-08-09 19:23:12', NULL);

CREATE TABLE `lx02` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `material` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `materialDescripcion` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bUn` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `totalStock` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `storageBin` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `lx02` (`id`, `material`, `materialDescripcion`, `bUn`, `totalStock`, `type`, `storageBin`, `created_at`, `updated_at`, `deleted_at`) VALUES
(6379, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6380, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6381, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6382, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6383, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6384, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6385, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6386, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6387, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6388, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6389, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6390, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6391, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6392, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6393, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6394, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6395, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6396, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6397, '486298', '8-15 X .750 TR CR A', 'EA', '18,000', 'PTE', 'PS3Q2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6398, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6399, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6400, '486298', '8-15 X .750 TR CR A', 'EA', '12,000', 'PTE', 'PS3O3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6401, '486298', '8-15 X .750 TR CR A', 'EA', '15,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6402, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6403, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6404, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6405, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6406, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6407, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6408, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6409, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6410, '486298', '8-15 X .750 TR CR A', 'EA', '18,000', 'PTE', 'PS6J5', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6411, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6412, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6413, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6414, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6415, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6416, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6417, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6418, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6419, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6420, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6421, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6422, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6423, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6424, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6425, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6426, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6427, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'PTE', 'PS6R3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(13008, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'STP', '34145858', '2021-08-09 22:43:57', '2021-08-09 22:43:57', NULL),
(13009, '486298', '8-15 X .750 TR CR A', 'EA', '1,260', 'STP', '34145857', '2021-08-09 22:43:57', '2021-08-09 22:43:57', NULL),
(13010, '486298', '8-15 X .750 TR CR A', 'EA', '6,000', 'STP', '34145856', '2021-08-09 22:43:57', '2021-08-09 22:43:57', NULL),
(6428, '488208', '8-15 X .813 HW A', 'EA', '1,000', 'PTE', 'SJTA3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6429, '488208', '8-15 X .813 HW A', 'EA', '1,000', 'PTE', 'SJTA3', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6430, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6431, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6432, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6433, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6434, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6435, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6436, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6437, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6438, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6439, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6440, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL),
(6441, '488208', '8-15 X .813 HW A', 'EA', '6,000', 'PTE', 'PS5C2', '2021-08-09 22:43:50', '2021-08-09 22:43:50', NULL);

Statement 1

SELECT  parte, 
        sum(cantidad) 
FROM directoscount 
WHERE deleted_at IS NULL 
GROUP BY parte
parte  | sum(cantidad)
:----- | ------------:
486298 |            56
488208 |            77

Statement 2

SELECT  material, 
        sum(totalStock) 
FROM lx02 
WHERE deleted_at is null 
GROUP BY material
material | sum(totalStock)
:------- | --------------:
486298   |             346
488208   |              74

Combined Statements

SELECT   directoscount.parte, 
         SUM(directoscount.cantidad), 
         lx02.material, 
         SUM(lx02.totalStock) 
FROM directoscount 
    LEFT JOIN lx02 
        ON directoscount.parte = lx02.material 
WHERE directoscount.deleted_at IS null 
AND            lx02.deleted_at IS null 
GROUP BY directoscount.parte, lx02.material
parte  | SUM(directoscount.cantidad) | material | SUM(lx02.totalStock)
:----- | --------------------------: | :------- | -------------------:
486298 |                        2912 | 486298   |                 1038
488208 |                        1078 | 488208   |                  222

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can join the queries as subselects, that first cacluate the data.

select
*
FROM
(select parte, sum(cantidad) from directoscount WHERE deleted_at is null group by parte) t1
LEFT JOIN
(select material, sum(totalStock) from lx02 where deleted_at is null group by material) t2
ON t1.parte = t2.material
parte  | sum(cantidad) | material | sum(totalStock)
:----- | ------------: | :------- | --------------:
486298 |            56 | 486298   |             346
488208 |            77 | 488208   |              74

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply