SQL - select parent and child records in an order
how to get parent and child record in single query using sql
sql recursive query to find parent
sql hierarchy query child parent without cte
sql order by parent child relationship
how to get parent and child record in single query using oracle
sql grandparent parent-child
query to find parent child tables in sql server
I got a table which has both question Id and parent question ids in the same table as :
QUES_ID | Ques_Txt | Parent_Ques_Id --------+-----------+---------+--------------- 97 | X | NULL 101 | Y | NULL 115 | Z | NULL 198 | A | 97 302 | B | 97 151 | C | 101 91 | D | 115 1110 | E | 115 1111 | F | 115
Now, I want to get a result set as follows in the following order:
QUES_ID | Ques_Txt | Parent_Ques_Id --------+-----------+---------+--------------- 97 | X | NULL 198 | A | 97 302 | B | 97 101 | Y | NULL 151 | C | 101 115 | Z | NULL 91 | D | 115 1110 | E | 115 1111 | F | 115
Having parent question listed right above the child question, I have researched for a similar query but all i found is how to display parent-child records using left joins or CTEs, but not in the order that I'm looking for as in the above table. Can any one help, I'm still learning.
You can do this using a trick with
select * from t order by coalesce(parent_ques_id, ques_id), parent_ques_id, ques_id
Sorting logic would be:
- Sort all parent-child relations one after another (97, 101, 115)
coalesce(parent_ques_id, ques_id)- assign a parent it's own id (instead of null) to be sorted with his children
- Make parent appear on top of each group
parent_ques_id- nulls sort before values in ASC order as default (in SQL Server)
- Sort children by their id
Parent Child Relation in same Table, SELECT a.custid, a.custname, a.deptid, a.company, b.custid AS bcustid, Since you know that parents are enrolled and children not, returing the enrolled + custid for the children and order only by orderKey to get well ordered records. A common way to do this in SQL Server is to use a Recursive CTE:. SELECT Id, Name FROM Employee WHERE CompanyId in (SELECT Id FROM Company) Now I have tried the above, which does what your given query is required to do. But for the desired results on the basis of Comapny/ParentCompany, please try the following query.
order by clause :
order by (case when Parent_Ques_Id is null then QUES_ID else Parent_Ques_Id end), Parent_Ques_Id, QUES_ID;
Selecting parent records with specific subset of child records , Imagine this kind of SQL query - you have a parent/child tables such How can you create SQL query that returns parent row and exactly two child rows? Then, you will need order employees by date hired (or salary - this is In SQL Server it took 86 seconds compared to the 3 statements logic (like below) which took approximately 5 minutes. insert into 1 st parent table + store output into the variable. insert into 2 st parent table + store output into the variable. insert into a child table.
OK, here is how I would do it, this version handles up to 99 siblings. As I said, it's complicated.
;WITH cte AS ( SELECT QUES_ID, Quest_Txt, Parent_Quest_Id, --(assumes max 9 siblings) RIGHT('0'+CAST(ROW_NUMBER()OVER(PARTITION BY Parent_Quest_Id ORDER BY Quest_Txt) AS VARCHAR(MAX)), 1) AS [path], 0 AS level FROM Questions WHERE Parent_Quest_Id = 0 UNION ALL SELECT t.QUES_ID, t.Quest_Txt, t.Parent_Quest_Id, [path] +'-' --(assumes max 99 siblings) + RIGHT('00'+CAST(ROW_NUMBER()OVER(PARTITION BY t.Parent_Quest_Id ORDER BY t.Quest_Txt) AS VARCHAR(max)), 2), level+1 FROM cte JOIN Questions t ON cte.QUES_ID = t.Parent_Quest_Id ) SELECT QUES_ID, Quest_Txt, Parent_Quest_Id, [path] FROM cte ORDER BY path
SQL Queries to Manage Hierarchical or Parent-child Relational , Here, we are trying to find all possible parents of a row where its Id = 5 . Starts with selecting the row where Id = 5; Find other rows where its id is I will show you how you can select one parent row with two latest child rows using T-SQL Introduction Imagine this kind of SQL query - you have a parent/child tables such as Product/ProductItem, Company/Employee, Article/Categories where parent and child tables are connected with one-to-many relationship.
ORDER SIBLINGS BY Clause, The ORDER SIBLINGS BY clause is valid only in a hierarchical query. specifies an order that first sorts the parent rows, and then sorts the child rows the ORDER BY clause is an extension to the ISO standard syntax for the SQL language. SQL Queries to Manage Hierarchical or Parent-child Relational Rows in SQL Server second is the subsequent parents and third column is the order. I have many
SQL Server Insert Parent and Child Records with One Statement, In this tip we look at an approach to load data into a parent and child But in SQL Server, the CTEs query definition must meet a view's To overcome the issue, I have used INSERT EXEC construction, but in order to use Oracle selects the child rows of each root row. Each child row must satisfy the condition of the CONNECT BY condition with respect to one of the root rows. Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on.
How to query parent rows when all children must match the filtering , Learn how to query and fetch parent rows when all associated child entries match the provided filtering criteria using both SQL and Hibernate. The number of records in the detail table for each parent record varies by Site. I need to select all of the data in the parent records and bring each child record together in the one row for the
- FYI, this is do-able, but it is a hard problem.
- Two questions, 1) what version of SQL Server? and 2) what is the maximum number of siblings/children with the same parent?
- I'm using SQL 2012, A given parent can have a maximum of 4 children based on the data I got
- How many levels of children can there be? (ex. can their be grand children?)
- No, there will not be any grand children but, one parent can have a maximum of four children
- This would work if the
ques_id'swere in order, but they aren't. For example
91would return before it's parent
- This only works if this is only one level of parents/children (ie., no grandchildren/grandparents).
- @sgeddes see rextester.com/NLKY17569 It's better to test it for the database it's supposed to work with, due to different default null handling in order by clause between Postgres and SQL Server.
- @RBarryYoung I've had this one before, but then 91 would sort before it's parent that has higher id (115) which sgeddes pointed out (thanks), hence the next column to sort by
- @KamilG. Right, OP added it in the comments after I had already started writing my answer.
- Oops, missed that there's no grandparents/grandchildren. If that's still true then @KamilG's solution is much simpler and preferred.