SQL - select parent and child records in an order

sql query parent child same table
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 coalesce:

select *
from t
order by coalesce(parent_ques_id, ques_id), parent_ques_id, ques_id

Sorting logic would be:

  1. 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
  2. Make parent appear on top of each group parent_ques_id - nulls sort before values in ASC order as default (in SQL Server)
  3. Sort children by their id ques_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.


Use 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