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, becuase the category_id
in that particular row has more than one child category_id
.
Here is a simplified example:
Table Wallet_balance_history
:
ID | category_id |
---|---|
1 | 4 |
2 | 4 |
Table categories
:
ID | parent_id | name |
---|---|---|
4 | null | x1 |
5 | 4 | x1.1 |
6 | 4 | x1.2 |
if 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
ID | c1 | c2 |
---|---|---|
1 | x1 | x1.1 |
1 | x1 | x1.2 |
I understand there is no way for the query to know the category_id for the child.
I thought of two options:
- 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. - 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