SQL - select parent and child records in an order
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