I'm trying to make database that contains products in storage. Every product contains unknown amount of possible "selections" that defines what version of product is in storage.
For example product Sofa
that contains selections Color: Black
, Legs: Wood
and Material: Leather
while product Bed
contains only selection Mattress: Hard
.
I've used GROUP_CONCAT to get all sofas that are same kind like this:
SELECT DISTINCT p.product, p.size, p.color,p.storage,
GROUP_CONCAT(s.selection, ': ' ,s.option) AS selection
FROM product p
INNER JOIN selection s
ON p.id = s.product_id
WHERE product.id = 123
GROUP BY s.id;
The question is how do I count how many products was concatenated in query or is it even possible to count with single query?
EDIT:
SELECT DISTINCT product_storage.product, product_storage.size,
product_storage.color, product_storage.storage,
GROUP_CONCAT(product_storage_selection.selection,
product_storage_selection.option) as selection
FROM product_storage_link
INNER JOIN product_storage
ON product_storage_link.product_storage_id = product_storage.id
INNER JOIN product_storage_selection
ON product_storage.id = product_storage_selection.product_storage_id
WHERE product_storage_link.product_id = 7207
GROUP BY product_storage.id;
Gives
Product Color Size Selection
Product1 Black 10x10 Selection1:Option1, Selection2:Option1
Product1 Black 10x10 Selection1:Option2, Selection2:Option1
Product1 White 10x10 Selection1:Option1, Selection2:Option1
I try to get count of duplicates in these two tables and got same selections from other table. Hope this clears the question.
So the goal is to get for example:
Product Color Size Selection amount
Product1 Black 10x10 Selection1:Option1, Selection2:Option1 3
Product1 Black 10x10 Selection1:Option2, Selection2:Option1 1
Product1 White 10x10 Selection1:Option1, Selection2:Option1 2
The part from database:
Aucun commentaire:
Enregistrer un commentaire