T-SQL Parent Child
-
Hi All,
I have 5 country grouping tables with PK & FK foriegn key relationships that store master group names and sub group names each with the relevant country id.
Though not all master groups are not required to have a sub group. In this is example ASEAN doesn't have a sub group and CEFTA does.
Master Groups
[dbo].[starui_country_mastergroup], ID:13 Name: ASEAN
ID:14 Name: CEFTA
[dbo].[starui_country_mastergroup_code] master group id: 13 country_id: 20,24,75.....
master group id: 14 country_id: 6,7,8.....
Sub Groups
[dbo].[starui_country_subgroup], ID: 29 name: CEFTA Subgroup
[dbo].[starui_country_subgroup_code], ID:29 country_id: 2,227,228....
Bridge Table
[dbo].[starui_country_mastergroup_subgroup]
My desired result is to have each sub group as a child under the parent with a country id for each row. So when I join it to the fact table on country id the sales amount can be totalled for the master group and sub group togther in a hierarchy or matrix table.
Hopefully this clear and I have attached in a zip file the create table and insert scripts.
Thanks for any assistance.
- ringovski wrote:
I have 5 country grouping tables with PK & FK foriegn key relationships that store master group names and sub group names each with the relevant country id.
There are 6 tables in the picture, no? It appears the hierarchy within groups is being derived (without constraints?) from some tables which were not provided. Issue #1 is the word UNIQUE doesn't appear anywhere in the zip script. There are primary keys and foreign keys but there are no alternate or constrained natural keys. For the tables ending in _CODE and for the table ending in _MASTERGROUP_SUBGROUP the absence of unique column(s) constraints make the data model impractical to query reliably imo
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
there should not be a column with csv content in a 4 NF relational table.
Have a look at parent - child relationships in Codd's relational theory with normalforms
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the reply.
Yeah the 6th table stores the codes and isn't need for this task. Unofrtunaely I didn't create this just inherited it, I have already my concerns with the Dev and he doesn't listen.
-
I've come up with this T-SQL but it doesn't show the master group country id only the sub group country id. I need both in the country id column.
with cte_head as (
select m.[name]
,mc.[master group id]
,mc.country_id
,b.
from [dbo].[starui_country_mastergroup] m
join [dbo].[starui_country_mastergroup_code] mc
ON m.id = mc.[master group id]
left join [dbo].[starui_country_mastergroup_subgroup] b
ON mc.[master group id] =b.[master group id]
),
CTE_Lines as (
SELECT s.id
,s.name
,'1' AS Rownum
,sc.country_id
--,ROW_NUMBER() OVER(PARTITION BY s.id,s.name ORDER BY s.id ASC) AS Rowl
from [dbo].[starui_country_subgroup] s
left join [dbo].[starui_country_subgroup_code] sc
on sc. = s.id
)
SELECT h.name As MasterGroup_Name
,l.name AS SubGroup_Name
,CASE WHEN RowNum = 1 THEN l.country_id else h.country_id END AS Country_Id
FROM cte_head h
LEFT JOIN CTE_Lines l
on l.id = h. -
I'm hoping that the errors in your attached script aren't going to matter... also, the code you posted above doesn't run due to all of the errors in that.
Msg 1767, Level 16, State 0, Line 154
Foreign key 'FK_starui_country_mastergroup_code_starui_country_code' references invalid table 'dbo.starui_country_code'.
Msg 1750, Level 16, State 1, Line 154
Could not create constraint or index. See previous errors.
Msg 4917, Level 16, State 0, Line 157
Constraint 'FK_starui_country_mastergroup_code_starui_country_code' does not exist.
Msg 4916, Level 16, State 0, Line 157
Could not enable or disable the constraint. See previous errors.
Msg 1767, Level 16, State 0, Line 174
Foreign key 'FK_starui_country_subgroup_code_starui_country_code' references invalid table 'dbo.starui_country_code'.
Msg 1750, Level 16, State 1, Line 174
Could not create constraint or index. See previous errors.
Msg 4917, Level 16, State 0, Line 177
Constraint 'FK_starui_country_subgroup_code_starui_country_code' does not exist.
Msg 4916, Level 16, State 0, Line 177
Could not enable or disable the constraint. See previous errors.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Your dbo,starui_country_mastergroup_subgroup table appears to be missing some rows compared to the example you gave, as well.
Please fix your script.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I resolved it with the following code.
SELECT
mg.[name] AS group_name,
NULL AS subgroup_name,
cmc.country_id
FROM dbo.starui_country_mastergroup mg
INNER JOIN dbo.starui_country_mastergroup_code cmc
ON mg.id = cmc.[master group id]
UNION ALL
SELECT
mg.[name] AS group_name,
sg.[name] AS subgroup_name,
csc.country_id
FROM dbo.starui_country_mastergroup mg
INNER JOIN dbo.starui_country_mastergroup_subgroup mgs
ON mg.id = mgs.[master group id]
INNER JOIN dbo.starui_country_subgroup sg
ON mgs. = sg.id
INNER JOIN dbo.starui_country_subgroup_code csc
ON sg.id = csc.
ORDER BY group_name, subgroup_name, country_id; -
Hey there why so hasty? If you'd fixed the script we might've seen some JM t-SQL It's been a while afaik :). The query seems nicely done tho. Presumably uniqueness constraints are a nonissue. Sorry to hear the Dev doesn't listen
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply