I have a database with these tables, where each mapping table represents a "many-to-one" relationship between the table on the right and the table on the left (click to enlarge):
I am selecting a single item from product
and then adding on all of the other attributes through joins. Here is my current SQL:
SELECT
p.id,
p.name,
p.price,
p.description,
GROUP_CONCAT(DISTINCT c.name SEPARATOR ",") AS categories,
GROUP_CONCAT(og.options SEPARATOR ";") AS options
FROM
product p
LEFT JOIN product_category pc ON p.id = pc.product_id
LEFT JOIN category c ON c.id = pc.category_id
LEFT JOIN product_optiongroup pog ON p.id = pog.product_id
LEFT JOIN (
SELECT
og.id AS id,
CONCAT(
og.name,
":",
GROUP_CONCAT(oi.value)
) AS options
FROM
optiongroup og
LEFT JOIN optiongroup_optionitem ogoi ON og.id = ogoi.optiongroup_id
LEFT JOIN optionitem oi ON oi.id = ogoi.optionitem_id
GROUP BY og.id
) AS og ON og.id = pog.optiongroup_id
WHERE
p.id = 1 -- This is the input
My desired output is something like:
id name price description categories options ------------------------------------------------------------------------------------------- 1 Big Hat 10.00 A good hat. Hats,Clothing Color:Black,White;Style:Baseball,Sun
My concern is, would this be a terrible pain to maintain? Are there simpler solutions? Also, does it need to be optimized & how? I don't have much experience with SQL. The output format isn't set in stone; I'm working solo.
LEFT JOIN
so much? Do you expect products to not have references in those tables a lot?LEFT JOIN
are quite expensive and should be used only when needed \$\endgroup\$