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
:
ID | category_id |
---|---|
1 | 4 |
2 | 4 |
Table categories
:
ID | parent_id | name |
---|---|---|
4 | null | x1 |
5 | 4 | x1.1 |
6 | 4 | x1.2 |
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:
ID | c1 | c2 |
---|---|---|
1 | x1 | x1.1 |
1 | x1 | x1.2 |
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:
ID | c1 | c2 |
---|---|---|
1 | x1 | x1.1 |
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:
- 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
Leave feedback about this