Let's say you have people, pets, and belongings. A person can have many pets and can also have many belongings.
What is the preferred way to fetch all people (along with their pets and belongings)?
Just LEFT JOINING on the pets and belongings tables will lead to way more rows than is necessary, because we will have a row for each possible pet-belonging combination for a given person.
I have a solution that uses GROUP BY, GROUP_CONCAT, and sub-queries, but I'm not sure this is ideal for performance.
For example, is there a performant way to avoid the following:
Person | Pet | Belonging
Nick | Fido | Hat
Nick | Fido | Car
Nick | Fido | House
Nick | Scruffy | Hat
Nick | Scruffy | Car
Nick | Scruffy | House
My aforementioned "GROUP BY, GROUP_CONCAT, and sub-query" solution would produce the following, but again, I'm worried about the performance of this approach
Person | Pets | Belongings
Nick | Fido,Scruffy | Hat,Car,House