May 24, 2013 at 7:03 am
Hello,
In my limited experience I have used a cte to write simple hierarchical result sets but this one is a bit more complicated for me. My child data records have a primary parent record, a secondary and tertiary (no more than that). This dataset will be bound to a tree for user interaction. So as mentioned in the past I had one parent and one child but this time I need to "repeat" the recursion for each child record to be recorded under each potential parent.
Data Child records:
FoodNameID | FoodName | FoodShortName | FoodGroupID | FoodGroupID2 | FoodGroupID3
56743 | Alumelle | Omelette | 1001 | 1007 | 1009
Data Parent Records:
FoodGroupID | FoodGroupName | FoodGroupDesc
1001 | Breakfast | Big Meal
1007 | High Protein | dasfsdsdf
1009 | Eggerific | qerrewqer
One thought I did have was to have a one to many relational table such as FoodName_FoodGroup with said structure:
FoodNameId | FoodGroupId
56743 | 1001
56743 | 1007
56743 | 1009
If using this "join" table then the query would be pretty straight forward.
Ultimately I would like to learn / use the industry best practice which is somewhat subjective but sometimes there are real obvious ones like join table vs rbar cursors.
Thank You
JB
May 24, 2013 at 8:38 am
The tables your describe in your sample are not hierarchical in the sense of the term usually applied to database tables, which is this: a self-referencing table in which for a given row the value of one column in the table contains the value of another column in the table for the parent (or child) row in the same table. To traverse such a structure sometimes requires a recursive CTE or sometimes multiple joins to the same table in the same FROM clause.
On the other hand, for your situation, three queries "stacked" on each other using UNION ALL is all you need.
with
Foods (FoodNameID,FoodName,FoodShortName,FoodGroupID,FoodGroupID2,FoodGroupID3) as
(select 56743, 'Alumelle', 'Omelette', 1001, 1007, 1009),
FoodGroups (FoodGroupID,FoodGroupName,FoodGroupDesc) as
(select 1001, 'Breakfast', 'Big Meal' union all
select 1007, 'High Protein', 'dasfsdsdf' union all
select 1009, 'Eggerific', 'qerrewqer')
select
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on Foods.FoodGroupID = FoodGroups.FoodGroupID
union all
select
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on Foods.FoodGroupID2 = FoodGroups.FoodGroupID
union all
select
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on Foods.FoodGroupID3 = FoodGroups.FoodGroupID
May 24, 2013 at 8:55 am
You could simplify the great example from Geoff (and much thanks for putting together consumable data).
select
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on Foods.FoodGroupID = FoodGroups.FoodGroupID or foods.FoodGroupID2 = FoodGroups.FoodGroupID or Foods.FoodGroupID3 = FoodGroups.FoodGroupID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 8:59 am
Or even less coding...
select
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on Foods.FoodGroupID in (Foods.FoodGroupID, foods.FoodGroupID2, Foods.FoodGroupID3)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 9:06 am
Two questions.
1st what did I fail to provide in terms of "consumable data" that Geoff put together. The actual query? The reason I ask is as the OP I should provide everything needed so I want to make sure to do better.
2nd. So the queries get my two tables bunched up nicely into one recordset but I still need to get the hierarchy so the front end dev can bind the results to a tree control....right???
Geoff thank you for clarifying the hierarchy table definition. That helped me see why I was at a dead end.
May 24, 2013 at 9:16 am
Sean, that last solution is an elegant way to collapse the three UNIONed queries together. Kudos on seeing the simpler way.
JB, I'm not sure what the results need to look like exactly to match up with the "tree" structure you mention. Perhaps a sample of the ideal final output you are looking for would help.
As for "consumable data," this means that someone can copy-and-paste code from your post into SSMS or some query environment and immediately begin working with it. I made your data consumable by moving it into two CTE queries. Most of the time I see people defining temp tables along with INSERT queries to put the data into the tables. I prefer CTEs myself, but whatever method you use, the idea is that the person who wants to help you doesn't have to spend time translating the sample data into a context that is queryable.
May 24, 2013 at 9:28 am
So for the foreseeable future we will only need two level. Parent Child so the tree would look like this:
FoodGroupA
----Food A
----Food B
FoodGroupB
----Food G
----Food K
FoodGroupC
----Food A
----Food K
Notice A & K belong to two parents. So given the queries now producing a happy recordset I was looking for something like this and was thinking to use Count Over() etc.
May 24, 2013 at 9:35 am
nfs_john (5/24/2013)
1st what did I fail to provide in terms of "consumable data" that Geoff put together. The actual query? The reason I ask is as the OP I should provide everything needed so I want to make sure to do better.
+10000
That attitude is awesome. So many people around here want us to do that for them. The explanation that Geoff gave is pretty good. If you want a more in depth look at the best practices for posting take a few minutes and read the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 9:37 am
nfs_john (5/24/2013)
So for the foreseeable future we will only need two level. Parent Child so the tree would look like this:FoodGroupA
----Food A
----Food B
FoodGroupB
----Food G
----Food K
FoodGroupC
----Food A
----Food K
Notice A & K belong to two parents. So given the queries now producing a happy recordset I was looking for something like this and was thinking to use Count Over() etc.
Can you post what you would want as desired output from the sample data you provided? Also if you can confirm that the consumable data that Geoff is correct that would be great. If it isn't, can you provide accurate sample data and ddl?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 10:19 am
Well first using the CTE for temp table to provide consumable data was a cool new thing I had never thought to use a cte for before.
The data portion is good but let me add a few more records.
with
Foods (FoodNameID,FoodName,FoodShortName,FoodGroupID,FoodGroupID2,FoodGroupID3) as
(
select 56743, 'Alumelle', 'Omelette', 1001, 1007, 1009 UNION ALL
select 56756, 'Yogurt', 'Good Bacteria', 1001, 1007, null UNION ALL
select 56735, 'Filet Mignon', 'Whats for dinner', 1002, null, null
),
FoodGroups (FoodGroupID,FoodGroupName,FoodGroupDesc) as
(select 1001, 'Breakfast', 'Big Meal' union all
select 1007, 'High Protein', 'dasfsdsdf' union all
select 1009, 'Eggerific', 'qerrewqer')
So now running produces triplicate even though some are null and should not have an entry. For example Filet & Yogurt show up under Eggerific. Putting together another sample from another angle.
May 24, 2013 at 10:24 am
Ahh in my last post I had accidentally created a cross join which wasn't noticeable until you added some missing/duplicates.
select
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on FoodGroups.FoodGroupID in (Foods.FoodGroupID, foods.FoodGroupID2, Foods.FoodGroupID3)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 24, 2013 at 10:25 am
The solution below is how I would tackle this for Reporting Services (SSRS). Other report environments may have different approaches, so hopefully this one is suitable to your particular reporting options. It is made up of two parts:
1) a "MainDataset" that is just like the earlier solutions given with one additional column: a "GroupAndFoodID" column that concatenates the two values together with a period in between (The reason for this column will be made clear later.)
2) a "SelectionParameter" dataset that takes two columns of output from the "MainDataset" and uses UNION to append the stand-alone FoodGroupsID (with a trailing period and asterisk) and FoodGroupsName.
If in SSRS you have the MainDataset as one report dataset and SelectionParameter as another, you can create a parameter that has query-provided values from the SelectionParameter dataset, with SelectionDescription as the label and SelectionID as the value. Then you can add a filter to the MainDataset dataset that uses a comparison of GroupAndFoodID LIKE SelectionID. With such a configuration, selecting the FoodGroup will select all the foods in that group, and selecting a particular food will only select that one item.
The reason for the period is to eliminate ambiguity that could arise if the two numbers were simply concatenated together with no separation.
By altering the SELECT line at the very end of the code, you can query either the MainDataset or the SelectionParameter CTE tables.
with
Foods (FoodNameID,FoodName,FoodShortName,FoodGroupID,FoodGroupID2,FoodGroupID3) as
(select 56743, 'Alumelle', 'Omelette', 1001, 1007, 1009),
FoodGroups (FoodGroupID,FoodGroupName,FoodGroupDesc) as
(select 1001, 'Breakfast', 'Big Meal' union all
select 1007, 'High Protein', 'dasfsdsdf' union all
select 1009, 'Eggerific', 'qerrewqer'),
MainDataset as
(select
GroupAndFoodID = CAST(FoodGroups.FoodGroupID AS varchar(4)) + '.' + CAST(Foods.FoodNameID as varchar(6)),
FoodGroups.FoodGroupID,
FoodGroups.FoodGroupName,
FoodGroups.FoodGroupDesc,
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods inner join
FoodGroups on FoodGroups.FoodGroupID in (Foods.FoodGroupID, Foods.FoodGroupID2, Foods.FoodGroupID3)),
SelectionParameter as
(select
SelectionID = cast(FoodGroups.FoodGroupID as varchar(4)) + '.*',
SelectionDescription = FoodGroupName
from
FoodGroups
union all
select
SelectionID = GroupAndFoodID,
SelectionDescription = '---' + MainDataset.FoodShortName
from
MainDataset)
select * from SelectionParameter order by SelectionID
-- select * from MainDataset
May 24, 2013 at 10:36 am
Your additional data points revealed that it is possible to have a food item with no related food group, at least in your example. So here is an updated solution that includes all foods, even those without a food group.
with
Foods (FoodNameID,FoodName,FoodShortName,FoodGroupID,FoodGroupID2,FoodGroupID3) as
(
select 56743, 'Alumelle', 'Omelette', 1001, 1007, 1009 UNION ALL
select 56756, 'Yogurt', 'Good Bacteria', 1001, 1007, null UNION ALL
select 56735, 'Filet Mignon', 'Whats for dinner', 1002, null, null
),
FoodGroups (FoodGroupID,FoodGroupName,FoodGroupDesc) as
(select 1001, 'Breakfast', 'Big Meal' union all
select 1007, 'High Protein', 'dasfsdsdf' union all
select 1009, 'Eggerific', 'qerrewqer'),
MainDataset as
(select
GroupAndFoodID = isnull(CAST(FoodGroups.FoodGroupID AS varchar(4)), '0000') + '.' + CAST(Foods.FoodNameID as varchar(6)),
FoodGroupID = isnull(FoodGroups.FoodGroupID, 0),
FoodGroupName = isnull(FoodGroups.FoodGroupName, 'Uncategorized'),
FoodGroupDesc = isnull(FoodGroups.FoodGroupDesc, 'Uncategorized'),
Foods.FoodNameID,
Foods.FoodName,
Foods.FoodShortName
from
Foods left join
FoodGroups on FoodGroups.FoodGroupID in (Foods.FoodGroupID, Foods.FoodGroupID2, Foods.FoodGroupID3)),
SelectionParameter as
(select
SelectionID = '0000.*',
SelectionDescription = 'Uncategorized'
union all
select
SelectionID = cast(FoodGroups.FoodGroupID as varchar(4)) + '.*',
SelectionDescription = FoodGroupName
from
FoodGroups
union all
select
SelectionID = GroupAndFoodID,
SelectionDescription = '---' + MainDataset.FoodName
from
MainDataset)
select * from SelectionParameter order by SelectionID
-- select * from MainDataset
May 28, 2013 at 11:39 am
Sorry I couldn't get this to work with CTE's holding the consumable data but here is a really close arrangement of the data in hierarchical form needed to bind to the tree control.
DECLARE @Foods TABLE(FoodNameID INT,FoodName VARCHAR(50),FoodShortName VARCHAR(50))
INSERT INTO @Foods VALUES(56743, 'Alumelle', 'Omelette')
INSERT INTO @Foods VALUES(56756, 'Yogurt', 'Good Bacteria')
INSERT INTO @Foods VALUES(56735, 'Filet Mignon', 'Whats for dinner')
DECLARE @FoodGroups TABLE(FoodGroupID INT,FoodGroupName VARCHAR(50),FoodGroupDesc VARCHAR(50))
INSERT INTO @FoodGroups VALUES(1001, 'Breakfast', 'Big Meal')
INSERT INTO @FoodGroups VALUES(1007, 'High Protein', 'dasfsdsdf')
INSERT INTO @FoodGroups VALUES(1009, 'Eggerific', 'qerrewqer')
INSERT INTO @FoodGroups VALUES(1002, 'Red Meat', 'qsdsa')
DECLARE @Foods_FoodGroups TABLE(FoodListID INT, FoodNameID INT, FoodGroupID INT, FoodParentID INT)
INSERT INTO @Foods_FoodGroups VALUES(1,null, 1001, null)
INSERT INTO @Foods_FoodGroups VALUES(2,56743, 1001, 1)
INSERT INTO @Foods_FoodGroups VALUES(3,null, 1007, null)
INSERT INTO @Foods_FoodGroups VALUES(4,56743, 1007, 3)
INSERT INTO @Foods_FoodGroups VALUES(5,null, 1009, null)
INSERT INTO @Foods_FoodGroups VALUES(6,56743, 1009, 5)
INSERT INTO @Foods_FoodGroups VALUES(7,56756, 1001, 1)
INSERT INTO @Foods_FoodGroups VALUES(8,56756, 1007, 3)
INSERT INTO @Foods_FoodGroups VALUES(9,null, 1002, null)
INSERT INTO @Foods_FoodGroups VALUES(10,56735, 1002, 9);
WITH FoodList AS
(
SELECT
ParentFFG.FoodListID,
ParentFFG.FoodNameID,
ParentFFG.FoodGroupID,
ParentFFG.FoodParentID
FROM
@Foods_FoodGroups ParentFFG
WHERE
ParentFFG.FoodParentID IS NULL
UNION ALL
SELECT
FFG.FoodListID,
FFG.FoodNameID,
FFG.FoodGroupID,
FFG.FoodParentID
FROM
@Foods_FoodGroups FFG
INNER JOIN FoodList AS FL ON
FFG.FoodParentID = FL.FoodListID
WHERE FFG.FoodParentID IS NOT NULL
)
SELECT
Flst.*,
Fgp.FoodGroupName,
Fgp.FoodGroupDesc,
Fds.FoodName,
Fds.FoodShortName
FROM
FoodList Flst
INNER JOIN @FoodGroups Fgp ON
Flst.FoodGroupID = Fgp.FoodGroupID
LEFT JOIN @Foods Fds ON
Fds.FoodNameID = Flst.FoodNameID
So this post is a bit of my hack at trying to properly arrange the data. My thought which you will see as the third table was to create some form of a "join hierarchy" table and work from that. I am of course very open to other ideas.
Thanks
JB
May 28, 2013 at 12:44 pm
If you want to list the options in a single column, the following can add one to your final query:
SelectionDescription = ISNULL('--- ' + Fds.FoodName, Fgp.FoodGroupName)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply