SQL query that flattens many-to-one relationships

Question

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.

Answer 1

Don't LEFT JOIN unless you really need to

In a comment I asked:

Is there a specific reason you are using 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 – Phrancis

To which you responded:

I don't expect it to happen a lot, but I do expect it to happen. In particular, there are products with no options. But there aren't any products with no categories. – 4castle

That being said, you probably ought to build into your script some checks for that. In plain English, something like this might make sense. You can use if/else blocks to make it work.

1. Check with only a left join on your options. This should cover a large amount of cases, based on your response.

2. Should that return no rows for an existing product, then try with more left joins to see if something else might be missing.

3. Should that still return no rows, or invalid data, throw an error.

---

Aliases

There are a few points from your code that I think can be improved. For instance FROM product p is not a good alias (consider the other aliases as well), as aliases should at least give a hint as to what they are referring to, and p really doesn't. FROM product AS prod would be better in this case.

Also notice the addition of AS keyword which is good practice. It's less of a big deal with table aliases, but it can cause issues in particular with column aliases if it is missing, so it's really best to just include it.