MySQL Forums
Forum List  »  General

Re: GROUP BY and NULL values
Posted by: Peter Brawley
Date: June 11, 2020 09:47AM

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`)
(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.

Options: ReplyQuote

Written By
June 10, 2020 12:50PM
Re: GROUP BY and NULL values
June 11, 2020 09:47AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.