Left join with hierarchical table produces duplicate rows

This question is building on previous one I asked here

I have the following tables based on the answer in the mentioned post (simplified):

This works perfectly when I want to query wallet_balance_history along with the first level parent category category_id references the top-level parent in categories table. What I am struggling with is, If I want to query lower level categories, I get duplicated rows for the same wallet_balance_history row, because the category_id in that particular row has more than one child category_id.

Here is a simplified example – table Wallet_balance_history:

Table categories:

When I run the following query:

select 
    wbh.id id, c1.name c1, c2.name c2, c3.name c3
from
    wallets_balance_history wbh 
left join 
    categories c1 on wbh.category_id = c1.id
left join 
    categories c2 on c2.parent_id = c1.id
left join 
    categories c3 on c3.parent_id = c2.id
where 
    wbh.id = 1;

I will get the following result set:

What I want to achieve is for every wallet_balance_history row (unique on ID), there should be at max one c1, one 2, one 3. So when I query the transactions I get something like this:

I know with the current design, I can’t achieve this. I understand there is no way for the query to know the category_id for the child.

I thought of two options:

1. In wallets_balance_history.category_id should have the lowest child (from child, I can find the parent category, but not necessarily the other way around if I have multiple children). The problem with this, is I won’t know what category level is there. It’s the lowest, but it could third, second of first, and it could create other complexities.
2. Resort to creating a bridging table where for each wallet_balance_history.id, is mapped to one or more rows depending on how many categories/child categories in that transaction.

You need to sign in to view this answers