one-to-many query selecting all parents and single top child for each parent
select p.id, p.text, c.id, c.parent, c.feature
from Parents p
left join (select c1.id, c1.parent, c1.feature
from Childs c1
join (select p1.id, max(c2.feature) maxFeature
from Parents p1
left join Childs c2 on p1.id = c2.parent
group by p1.id) cf on c1.parent = cf.id
and c1.feature = cf.maxFeature) c
on p.id = c.parent
Querying a one-to-many relationship such that all the children returned only belong to the parents returned in the query
You can do it relatively simply by comparing the Parent ID from the Child table to a sub-query. It's not the tidiest, but it should work for you. Obviously this is based on the ranking being 1 for best, etc.
SELECT p.id, c.id
FROM Child c
JOIN Parent p ON c.parent_id = p.id
WHERE c.parent_id
IN (SELECT id
FROM Parent
WHERE rank <= 20)
select parent and parent's child from the same table and order asc
You can try this way to get rows as you want.
SELECT c.title,ca.title,concat(c.id,'-',ca.id) as sort_column FROM
cat c
join cat as ca on ca.ana_cat = c.id order by sort_column
Microsoft Access: Query involving one parent and many children, select only the highest value child for each parent
It might be simpler to use a subquery similar to:
SELECT
*,
(SELECT TOP 1 StartDate
FROM Child AS T
WHERE T.RestrictivePracticeID = Parents.RestrictivePracticeID
ORDER BY T.StartDate DESC) AS LatestDate
FROM
Parents
Recursion for multiple Hierarchies and summing all parents
The way you've written the last query, it's only taking into account the immediately preceding ancestor in the hierarchy. Here's a trick that keeps track of all of the ancestors as you go:
with TotalMonthsForItemsAndParents (ID, Months, DirectParent, Months, RunningTotal) As
(
Select ID,
Months,
DirectParent,
Months,
Months as RunningTotal
From Sampletbl
Where DirectParent = 'Prop'
Union All
Select Sampletbl.ID,
Sampletbl.Months,
Sampletbl.DirectParent,
Sampletbl.Months,
TotalMonthsForItemsAndParents.RunningTotal + Sampletbl.Months
From Sampletbl
Join TotalMonthsForItemsAndParents on Sampletbl.DirectParent = TotalMonthsForItemsAndParents.ID
)
select *
from TotalMonthsForItemsAndParents
Order by ID;
For the base case, the RunningTotal value is just that row's Months value. For subsequent (i.e. the recursive step), it's the sum of this level's Months value and the previous level's RunningTotal.
Get all parents for a child
Try this to get all parents of a child
;with name_tree as
(
select id, parentid
from Users
where id = 47897 -- this is the starting point you want in your recursion
union all
select C.id, C.parentid
from Users c
join name_tree p on C.id = P.parentid -- this is the recursion
-- Since your parent id is not NULL the recursion will happen continously.
-- For that we apply the condition C.id<>C.parentid
AND C.id<>C.parentid
)
-- Here you can insert directly to a temp table without CREATE TABLE synthax
select *
INTO #TEMP
from name_tree
OPTION (MAXRECURSION 0)
SELECT * FROM #TEMP
Click here to view result
EDIT :
If you want to insert into a table variable, you can do something like:
-- Declare table varialbe
Declare @TABLEVAR table (id int ,parentid int)
;with name_tree as
(
select id, parentid
from #Users
where id = 47897 -- this is the starting point you want in your recursion
union all
select C.id, C.parentid
from #Users c
join name_tree p on C.id = P.parentid -- this is the recursion
-- Since your parent id is not NULL the recursion will happen continously.
-- For that we apply the condition C.id<>C.parentid
AND C.id<>C.parentid
)
-- Here you can insert directly to table variable
INSERT INTO @TABLEVAR
select *
from name_tree
OPTION (MAXRECURSION 0)
SELECT * FROM @TABLEVAR
Click here to view result
parent-child hierarchy with multiple parents in ORACLE
Add an OR condition to the CONNECT BY clause to check if PRIOR code BETWEEN etc1 and etc2:
with t as (
select 'A1' CODE, 'JOB_1_1' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'A2' CODE, 'JOB_1_2' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'A3' CODE, 'JOB_1_3' CODE_NM, 'A' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B01' CODE, 'JOB_2_1' CODE_NM, 'A1' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B02' CODE, 'JOB_2_2' CODE_NM, 'A2' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'B03' CODE, 'JOB_2_3' CODE_NM, 'A3' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C001' CODE, 'JOB_3_1' CODE_NM, 'B01' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C002' CODE, 'JOB_3_2' CODE_NM, 'B02' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'C003' CODE, 'JOB_3_3' CODE_NM, 'B03' PARENT_ID, NULL ETC1, NULL ETC2 from dual union all
select 'D0001' CODE, 'JOB_4_1' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all
select 'D0002' CODE, 'JOB_4_2' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual union all
select 'D0003' CODE, 'JOB_4_3' CODE_NM, 'C999' PARENT_ID, 'C001' ETC1, 'C003' ETC2 from dual
)
SELECT LPAD(' ', 4*(LEVEL-1)) || CODE AS CODE, CODE_NM, PARENT_ID, LEVEL
FROM t
START WITH PARENT_ID = 'A'
CONNECT BY
PRIOR CODE = PARENT_ID
OR PRIOR CODE BETWEEN ETC1 AND ETC2
;
Outputs:
CODE CODE_NM PARENT_ID LEVEL A1JOB_1_1 A 1 B01JOB_2_1 A1 2 C001JOB_3_1 B01 3 D0001JOB_4_1 C999 4 D0002JOB_4_2 C999 4 D0003JOB_4_3 C999 4 A2JOB_1_2 A 1 B02JOB_2_2 A2 2 C002JOB_3_2 B02 3 D0001JOB_4_1 C999 4 D0002JOB_4_2 C999 4 D0003JOB_4_3 C999 4 A3JOB_1_3 A 1 B03JOB_2_3 A3 2 C003JOB_3_3 B03 3 D0001JOB_4_1 C999 4 D0002JOB_4_2 C999 4 D0003JOB_4_3 C999 4 How to group parent/child records by flag and collapse to a single record for the combo where the flag is true if one or more expanded was true?
Actually, I just worked it out:
SELECT ParentID, ChildID, MAX(Flag) AS Flag
FROM @InMemoryResultsFirstPass
GROUP BY ParentID, ChildID
ORDER BY ParentID, ChildIDHopefully this helps someone else :-)
Related Topics
Get Month and Year from a Datetime in SQL Server 2005
Store Select Query's Output in One Array in Postgres
Unwrap Postgresql Array into Rows
Postgresql Changing Data Directory in Ubuntu
How to List User Defined Types in a SQL Server Database
Calling Stored Procedure from Another Stored Procedure SQL Server
How to Add Time to Datetime in SQL
Table Scan and Index Scan in SQL
Cannot Use Group by and Over(Partition By) in the Same Query
Difference Between Drop Table and Truncate Table
Cannot Get Simple Postgresql Insert to Work
Fast Relational Method of Storing Tree Data (For Instance Threaded Comments on Articles)
Creating a Udf(User Define Function) If Is Does Not Exist and Skipping It If It Exists
How to Count the Number of Times a Character Appears in a SQL Column