Re: GROUP BY and NULL values

[Peter Brawley]

The problem seems to arise from a failure to instantiate the concept of an order item in your schema design ... from what you say, you need an order_items table each row of which points to one order and one product. Instead, you are relying on style, colour &c to define items, and as you observe, it's not working.

Note: Spam is everywhere so these fora try to minimise use of externalise links ... for readers' safety & convenience here's the DDL mentioned ...

CREATE TABLE `order` (
order_id integer NOT NULL
);
CREATE TABLE order_product (
order_id integer NOT NULL,
quantity INT(11) NOT NULL,
order_product_id int(11) NOT NULL,
name varchar(55) NOT NULL
);
CREATE TABLE order_option (
order_id integer NOT NULL,
order_product_id int(11) NOT NULL,
name varchar(255) NOT NULL,
`value` varchar(55) NOT NULL
);
INSERT order_option(order_id, order_product_id, name, `value`)
VALUES
(12345, 101, 'Color - Current', 'Classic Red'),
(12345, 101, 'Color - Vintage', 'Classic Red'),
(12346, 102, 'Color', 'Grey'), (12346, 102, 'Collar', 'Y');
INSERT order_product (order_id, quantity, order_product_id, name)
VALUES (12345, 1, 101, 'ProductA'),
(12345, 1, 101, 'ProductA'), (12346, 1, 102, 'ProductB');
INSERT `order` (order_id) VALUES (12345), (12346);



Edited 1 time(s). Last edit at 06/11/2020 09:51AM by Peter Brawley.