Skip to content
Advertisement

SQL Select parent as column name and child as value

I am creating a database to store music.

There are different categories that each have sub categories. Example:

On the other hand I have a table that stores the musicID that is linked to a categoryID

I need the following result from a query:

I have been told to use a tree structure as in the future it is likely that other categories are added and this should be able to support that. However, I am not able to figure out how to write a query to get the result above.

I kind of get the result I want when selecting first the instrumentation, second the solo_instrument, but this is all hardcoded and does not allow for music tracks to only have one parentID as I select them individually.

Is this even possible or should I overhaul my database structure? I’d like to see your recommendations.

Advertisement

Answer

You should be able to tackle this using conditional aggregation.

Query :

The INNER JOINs pull up the corresponding category, and then goes up one level to find the parent category. If new root categories are created, you would just need to add more MAX() columns to the query.

In this DB Fiddle demo with your sample data, the query returns :

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement