I have 4 tables related to each other.
Table location:
CREATE TABLE `location` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `location` (`id`, `name`) VALUES
(1, 'Dallas'),
(2, 'Boston'),
(3, 'Houston');
Table item:
CREATE TABLE `item` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`brand` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `item` (`id`, `brand`) VALUES
(1, 'Nissan Almera M/T 2009-2015'),
(2, 'Toyota Corolla A/T 2005-2012'),
(3, 'Nissan Terra A/T 2010-2017'),
(4, 'Suzuki Esteem M/T 1980-1990'),
(5, 'Toyota Fortuner A/T 2014-2020');
Table item_in:
CREATE TABLE `item_in` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`location_id` bigint(20) UNSIGNED NOT NULL,
`item_id` bigint(20) UNSIGNED NOT NULL,
`quantity` int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `item_in` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 1, 1000),
(2, 1, 2, 500),
(3, 2, 2, 200),
(4, 2, 2, 300),
(5, 3, 3, 300),
(6, 1, 3, 800),
(7, 3, 5, 300),
(8, 3, 4, 400);
Table item_out:
CREATE TABLE `item_out` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`location_id` bigint(20) UNSIGNED NOT NULL,
`item_id` bigint(20) UNSIGNED NOT NULL,
`quantity` int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `item_out` (`id`, `location_id`, `item_id`, `quantity`) VALUES
(1, 1, 2, 20),
(2, 1, 1, 25),
(3, 2, 2, 25),
(4, 3, 3, 25),
(5, 3, 5, 10),
(6, 3, 4, 15),
(7, 1, 1, 200),
(8, 2, 2, 50);
Using dynamic SQL, I was able to get the individual remaining quantities per item based on their location and item (item_in quantity subtracted by item_out quantity) and have the location names as columns. (see code below):
SET @sql = NULL, @sql1 = NULL, @sql2 = NULL;
SELECT GROUP_CONCAT( DISTINCT
CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
INTO @sql1
FROM item_in
JOIN location on location.id = item_in.location_id;
SELECT GROUP_CONCAT( DISTINCT
CONCAT('SUM(CASE WHEN `location_id` = ''',`location_id`, ''' THEN quantity END) AS ',`name`))
INTO @sql2
FROM item_out
JOIN location on location.id = item_out.location_id;
SET @sql = CONCAT('SELECT item.brand AS Item, IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston FROM item LEFT JOIN (SELECT item_in.item_id, ', @sql1, ' FROM item_in
GROUP BY item_in.item_id) AS item_in ON item.id = item_in.item_id LEFT JOIN (SELECT item_out.item_id, ', @sql2, ' FROM item_out
GROUP BY item_out.item_id) AS item_out ON item.id = item_out.item_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Result:
Item | Dallas | Boston | Houston
Nissan Almera M/T 2009-2015 775 0 0
Toyota Corolla A/T 2005-2012 480 425 0
Nissan Terra A/T 2010-2017 800 0 275
Suzuki Esteem M/T 1980-1990 0 0 385
Toyota Fortuner A/T 2014-2020 0 0 290
My question, how do I go about changing the code so that the location name columns is displayed dynamically instead of hardcoding them manually in the query since users can add new locations anytime? if anyone can take a look at my code, i'd really appreciate the help. The only part I'm having trouble is how to not hardcode these lines and do them dynamically:
IFNULL(item_in.Dallas, 0) - IFNULL(item_out.Dallas, 0) AS Dallas, IFNULL(item_in.Boston, 0) - IFNULL(item_out.Boston, 0) AS Boston, IFNULL(item_in.Houston, 0) - IFNULL(item_out.Houston, 0) AS Houston
Best Answer
This is a possible solution in PHP: