I have a resource table that contains resource values and descriptions. The table also has a parent_id column that can match a res_id of another resource. This table can go up to 4 levels deep of children.
Sample resource table:
| res_id| kind_id| value | description | parent_id|
|-------|--------|---------------|-------------|----------|
| 5678 | 4 | price_asfa | fgdfhdf | null |
| 6789 | 2 | number_val | djhhjhj | 2345 |
| 9876 | 5 | test_val | lutuyutyu | 5678 |
| 8765 | 2 | other_val | yuiyuiy | 9876 |
| 26346 | 3 | edit_kind | yfgjqwes | null |
| 1234 | 2 | option_kind | dggdfghdhg | 2345 |
| 2345 | 3 | fruit_price | ghfgndfd | 3456 |
| 3456 | 5 | console_price | fgdfgdsffhg | 5678 |
I also have a users table and a many-many user_resource table to create a relationship between the user_id and the res_id
Sample user table:
| user_id | username | password | user_type_id |
|---------|-----------|----------|--------------|
| 100 | tim.smith | sdfsg | 5 |
| 200 | jon.doe | sdfsdh | 4 |
| 300 | alex.din | tyurte | 5 |
Sample user_resource table
| user_id | res_id | date |
|---------|--------|------|
| 100 | 3456 | any |
| 300 | 1234 | any |
| 200 | 8765 | any |
| 100 | 26346 | any |
Im looking to get an SQL output where each row can see the username and userid as well as 4 columns (one column for each level resource description). not all resources will go 4 levels deep so some columns will have null values.
Im running into a situation when trying to join all the tables that im seeing duplicate resources moved up one level in the column rows. So im trying to nest the resource selects but its causing issues and im not able to create this table.
Expected output. (basically break down the relationships in the resource table into 4 columns as its max only 4 levels deep on parent/child/grandchild):
| user_id | username | resource_group | resource1 | resource2 | resource3 |
|---------|-----------|----------------|-------------|---------------|------------|
| 100 | tim.smith | console_price | price_asfa | null | null |
| 100 | tim.smith | edit_kind | null | null | null |
| 200 | jon.doe | other_val | test_val | price_asfa | null |
| 300 | alex.din | option_kind | fruit_price | console_price | price_asfa |
| 300 | alex.din | edit_kind | null | null | null |
Ive made it one level down, but now if i try to nest a select join inside the other select join im getting invalid identifier errors as i guess the query cant find the nested values?
How can i keep this query going all the way down 4 levels?
SELECT u.userid,
u.username,
r.resource_group AS "RESOURCE GROUP",
r.resource1
FROM users_table u
inner join user_resources_table ur
ON u.userid = ur.userid
left join (SELECT r1.resid,
r1.description RESOURCE_GROUP,
r_r.description resource1
FROM resources_table r1
left join resources_table r_r
ON r_r.parentid = r1.resid
WHERE r1.parentid IS NULL) r
ON ur.resid = r.resid
WHERE u.active = 'Y'
AND u.usertypeid = 5
AND u.username = 'someName'
ORDER BY u.username,
r.resource_group,
r.resource1;
EDIT: update to Alex's response.
Currently now if I run the query as you've given alex i end up with something like this ():
| user_id | username | resource_group | resource1 | resource2 | resource3 |
|---------|-----------|----------------|---------------|-------------|---------------|
| 100 | tim.smith | null | null | price_asafa | console_price |
| 100 | tim.smith | null | null | null | edit_kind |
| 200 | jon.doe | null | price_asfa | test_val | other_val |
| 300 | alex.din | price_asfa | console_price | fruit_price | option_kind |
| 300 | alex.din | null | null | null | edit_kind |
When its supposed to be like this:
| user_id | username | resource_group | resource1 | resource2 | resource3 |
|---------|-----------|----------------|-------------|---------------|------------|
| 100 | tim.smith | console_price | price_asfa | null | null |
| 100 | tim.smith | edit_kind | null | null | null |
| 200 | jon.doe | other_val | test_val | price_asfa | null |
| 300 | alex.din | option_kind | fruit_price | console_price | price_asfa |
| 300 | alex.din | edit_kind | null | null | null |
So i reversed the lvl like so:
for lvl in (3 as grp, 2 as lvl1, 1 as lvl2, 0 as lvl3)
And now it is aligning like below:
| user_id | username | resource_group | resource1 | resource2 | resource3 |
|---------|-----------|----------------|-------------|---------------|------------|
| 100 | tim.smith | null | null | console_price | price_asfa |
| 100 | tim.smith | null | null | null | edit_kind |
| 200 | jon.doe | null | other_val | test_val | price_asfa |
| 300 | alex.din | option_kind | fruit_price | console_price | price_asfa |
| 300 | alex.din | null | null | null | edit_kind |
Which works if all rows are not null. but any nulls end up by default to the left of the data. See the first row for example. The values should be in resource_group and resource1 as noted in the example table above, but they are aligned on the right of the table.
Is there a way to reverse this?
CodePudding user response：
You can use recursive subquery factoring to generate the levels:
with rcte (user_id, username, root_res_id, lvl, res_id, parent_id, value) as (
select ut.user_id, ut.username, urt.res_id, 0, rt.res_id, rt.parent_id, rt.value
from users_table ut
join user_resources_table urt on urt.user_id = ut.user_id
join resources_table rt on rt.res_id = urt.res_id
union all
select r.user_id, r.username, r.root_res_id, r.lvl 1, rt.res_id, rt.parent_id, rt.value
from rcte r
join resources_table rt on rt.res_id = r.parent_id
)
select * from rcte
| USER_ID | USERNAME | ROOT_RES_ID | LVL | RES_ID | PARENT_ID | VALUE | DESCRIPTION |
|---|---|---|---|---|---|---|---|
| 100 | tim.smith | 3456 | 1 | 3456 | 5678 | console_price | fgdfgdsffhg |
| 100 | tim.smith | 3456 | 2 | 5678 | null | price_asfa | fgdfhdf |
| 100 | tim.smith | 26346 | 1 | 26346 | null | edit_kind | yfgjqwes |
| 200 | jon.doe | 8765 | 1 | 8765 | 9876 | other_val | yuiyuiy |
| 200 | jon.doe | 8765 | 2 | 9876 | 5678 | test_val | lutuyutyu |
| 200 | jon.doe | 8765 | 3 | 5678 | null | price_asfa | fgdfhdf |
| 300 | alex.din | 1234 | 1 | 1234 | 2345 | option_kind | dggdfghdhg |
| 300 | alex.din | 1234 | 2 | 2345 | 3456 | fruit_price | ghfgndfd |
| 300 | alex.din | 1234 | 3 | 3456 | 5678 | console_price | fgdfgdsffhg |
| 300 | alex.din | 1234 | 4 | 5678 | null | price_asfa | fgdfhdf |
And then pivot using the levels to get the format you want, renaming the pivoted columns to whatever you want:
with rcte (user_id, username, root_res_id, lvl, res_id, parent_id, value) as (
select ut.user_id, ut.username, urt.res_id, 0, rt.res_id, rt.parent_id, rt.value
from users_table ut
join user_resources_table urt on urt.user_id = ut.user_id
join resources_table rt on rt.res_id = urt.res_id
union all
select r.user_id, r.username, r.root_res_id, r.lvl 1, rt.res_id, rt.parent_id, rt.value
from rcte r
join resources_table rt on rt.res_id = r.parent_id
)
select user_id, username,
grp_value as resource_group,
lvl1_value as resource_1,
lvl2_value as resource_2,
lvl3_value as resource_3
from (
select user_id, username, root_res_id, lvl, res_id, value
from rcte
)
pivot (
max(res_id) as res_id, max(value) as value
for lvl in (0 as grp, 1 as lvl1, 2 as lvl2, 3 as lvl3)
)
order by user_id, root_res_id
| USER_ID | USERNAME | RESOURCE_GROUP | RESOURCE_1 | RESOURCE_2 | RESOURCE_3 |
|---|---|---|---|---|---|
| 100 | tim.smith | console_price | price_asfa | null | null |
| 100 | tim.smith | edit_kind | null | null | null |
| 200 | jon.doe | other_val | test_val | price_asfa | null |
| 300 | alex.din | option_kind | fruit_price | console_price | price_asfa |
If you want to reverse the order so that the resource at the root of the hierarchy - that is, the one with a null parent - as the resource group, then you can reverse the levels in the pivot subquery (not in the pivot clause itself):
with rcte (user_id, username, root_res_id, lvl, res_id, parent_id, value) as (
select ut.user_id, ut.username, urt.res_id, 0, rt.res_id, rt.parent_id, rt.value
from users_table ut
join user_resources_table urt on urt.user_id = ut.user_id
join resources_table rt on rt.res_id = urt.res_id
union all
select r.user_id, r.username, r.root_res_id, r.lvl 1, rt.res_id, rt.parent_id, rt.value
from rcte r
join resources_table rt on rt.res_id = r.parent_id
)
select user_id, username,
grp_value as resource_group,
lvl1_value as resource_1,
lvl2_value as resource_2,
lvl3_value as resource_3
from (
select user_id, username, root_res_id, res_id, value,
row_number() over (partition by user_id, root_res_id order by lvl desc) - 1 as lvl
from rcte
)
pivot (
max(res_id) as res_id, max(value) as value
for lvl in (0 as grp, 1 as lvl1, 2 as lvl2, 3 as lvl3)
)
order by user_id, root_res_id
| USER_ID | USERNAME | RESOURCE_GROUP | RESOURCE_1 | RESOURCE_2 | RESOURCE_3 |
|---|---|---|---|---|---|
| 100 | tim.smith | price_asfa | console_price | null | null |
| 100 | tim.smith | edit_kind | null | null | null |
| 200 | jon.doe | price_asfa | test_val | other_val | null |
| 300 | alex.din | price_asfa | console_price | fruit_price | option_kind |
But that isn't what you showed in the question.
CodePudding user response：
Almost exactly the same as my answer to your (now deleted) previous question, just reversing the hierarchy and then re-reversing the depths:
select u.user_id,
u.username,
r.*
FROM users_table u
INNER JOIN user_resources_table ur
on u.user_id = ur.user_id
CROSS JOIN LATERAL (
SELECT *
FROM (
SELECT value,
MAX(LEVEL) OVER () 1 - LEVEL AS depth
FROM resources_table r
WHERE level <= 4
START WITH ur.res_id = r.res_id
CONNECT BY res_id = PRIOR parent_id
)
PIVOT (
MAX(value)
FOR depth IN (
1 AS resource_group,
2 AS resource1,
3 AS resource2,
4 AS resource3
)
)
) r
--WHERE u.active = 'Y'
--AND u.user_type_id = 5
--AND u.username = 'someName'
ORDER BY
u.username,
r.resource_group,
r.resource1,
r.resource2,
r.resource3;
Which, for the sample data (thanks @AlexPoole), outputs:
USER_ID USERNAME RESOURCE_GROUP RESOURCE1 RESOURCE2 RESOURCE3 300 alex.din price_asfa console_price fruit_price option_kind 200 jon.doe price_asfa test_val other_val null 100 tim.smith edit_kind null null null 100 tim.smith price_asfa console_price null null
db<>fiddle here