Sql-server – Retrieve hierarchical data

sql server

I have the following tables:

Teachers:

+------+--------+
|  id  | deptId |
+------+--------+
|  1   |    2   |
+------+--------+
|  2   |    1   |
+------+--------+

Department:

+------+-----------+
|  id  | contactId |
+------+-----------+
|  1   |     2     |
+------+-----------+
|  2   |     6     |
+------+-----------+

Contact:

+----+--------------------+
| id | nonImportantColumn |
+----+--------------------+
| 2  |   street street    |
+----+--------------------+
| 4  |   street2street    |
+----+--------------------+

SchoolBranch:

+----+-----------+----------------+
| id | contactId | parentBranchId |
+----+-----------+----------------+
| 3  |    2      |     null       |
+----+-----------+----------------+
| 4  |    5      |     3          |
+----+-----------+----------------+
| 5  |    7      |     null       |
+----+-----------+----------------+
| 6  |    2      |     3          |
+----+-----------+----------------+

Given a teacher's ID let's say 2, I am trying to get the below depicted resultset format but don't know how to begin. I am not an expert.

+----------+-----------+----------------+--------------------+
| branchId | contactId | parentBranchId | nonImportantColumn |
+----------+-----------+----------------+--------------------+
|    3     |     2     |      null      |  some street adr   |
+----------+-----------+----------------+--------------------+
|    4     |     5     |        3       |  some street adr   |
+----------+-----------+----------------+--------------------+
|    6     |     2     |        3       |  some street adr   |
+----------+-----------+----------------+--------------------+

The result contains the data for branch id 4 and 6 as well because these branches are child branches of 3 (which is linked to our input teacher ID:2)

I am using SQL Server v 18, in case it helps.

Best Answer

You can use a CTE to create a dataset for SchoolBranch that includes the contactId value for the parent record then simply INNER JOIN to the main dataset to get the data you're after.

WITH CTE AS (
  SELECT id, contactId, parentBranchId, NULL AS parentContactId
  FROM SchoolBranch
  WHERE parentBranchId IS NULL
  UNION ALL
  SELECT sb.id, sb.contactId, sb.parentBranchId, p.contactId AS parentContactId
  FROM SchoolBranch sb
  INNER JOIN CTE p ON p.id = sb.parentBranchId
)

SELECT sb.id AS branchId
  , c.id AS contactId
  , sb.parentBranchId
  , c.NonImportantColumn
FROM Teachers t
INNER JOIN Department d ON d.id = t.deptId
INNER JOIN Contact c ON c.id = d.contactId
INNER JOIN CTE sb ON (sb.contactId = c.id) OR (sb.parentContactId = c.id)
WHERE t.id = 2

You can see a working example on db<>fiddle.

Related Solutions

Sql-server – Can’t retrieve data of countries and regions

Since you're trying to get into the SQL way of thinking, I'll try to break this down to explain how I got to the conclusion.

To find the answer behind this search, I tried to examine the data to see what I had to work with.

SELECT * FROM bbc

I found that what we really had to deal with was Population, Name (country name) and Region. The question stated that we need to find countries larger than others in the same region. So, I picked a random region and examined the data there:

SELECT * FROM bbc
WHERE region = 'South Asia'

Then I sorted the data by population so that I could try to understand what we're going to compare.

SELECT * FROM bbc
WHERE region = 'South Asia'
ORDER BY population DESC

If you're following from that page, you can see that India is dramatically larger than the next sized country, Pakistan.

Next, I (misunderstanding the question), found all countries that were three times the size of any other country in that region. However, I realized that question was really "three times the size of the smallest". So, I came up with

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population > 
        (SELECT MIN(b.population) * 3 
         FROM bbc AS b
         WHERE b.region = bbc.Region)
ORDER BY population DESC

There I'm finding the population where the size is greater than three times the minimum of that region. The correlated subquery (go research those) will limit the comparison to only select the populations in that region.

Finally, I realized that we're supposed to find the one that was three times larger than all other countries in the region (not just three times larger than ANY country in the region), so I changed my query to:

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population > 
        (SELECT MAX(population) * 3 
         FROM bbc AS b
         WHERE b.region = bbc.region 
           AND b.name <> bbc.name)
ORDER BY population DESC

That changed the "MIN" to "MAX" and excluded countries from the subquery that are the same country as the one we're comparing. So, when we're comparing India in the outer query, we're ignoring India in the inner query--thus finding the largest country that's Not India.

Finally, remove the "RegioN" from the outer where clause and only pull the two columns that they want (since they're picky):

SELECT name, region FROM bbc
WHERE population > 
        (SELECT MAX(population) * 3 
         FROM bbc AS b
         WHERE b.region = bbc.region 
           AND b.name <> bbc.name)
ORDER BY population DESC

Sql-server – Help to retrieve data set

Try the query below.

Query:

with ord as(
    SELECT ParRecId
        , m = ROW_NUMBER() over(partition by ParRecId, Created order by RecId)
        , r = DENSE_RANK() over(partition by ParRecId order by Created desc, RecId)
        , Created, [Status]
    FROM @data
)
SELECT o1.ParRecId
    , [START] = o1.Created
    , [END] = CASE WHEN o2.Status <> 'CANCEL' THEN o2.Created END
    , [STATUS] = CASE WHEN o2.Status IS NULL OR o2.Status = 'CANCEL' THEN 'OPEN' ELSE 'CLOSED' END      
FROM ord o1
LEFT JOIN ord o2 
    ON o2.ParRecId = o1.ParRecId 
        AND o2.r = o1.r-1 
WHERE o1.[Status] = 'CANCEL'
    AND (o1.Created <> o2.Created OR o2.Created IS NULL)
ORDER BY o1.ParRecId, o1.Created ASC

Your data:

declare @data table(RecId int, ParRecId char(1), Created date, [Status] varchar(10));
INSERT INTO @data(RecId, ParRecId, Created, [Status]) values
    (17,'A', '2015-11-26', 'CANCEL')
    , (16, 'A', '2015-11-26', 'INPROGRESS')
    , (15, 'B', '2015-11-25', 'Active')
    , (14, 'B', '2015-11-25', 'CANCEL')
    , (13, 'B', '2015-11-25', 'ON-HOLD')
    , (12, 'B', '2015-11-25', 'ON-HOLD')    
    , (12, 'E', '2015-11-25', 'CANCEL')
    , (11, 'D', '2015-11-25', 'ACTIVE')
    , (10, 'C', '2015-10-19', 'CANCEL')  
    , (9, 'C', '2015-09-19', 'ACTIVE')
    , (8, 'C', '2015-09-12', 'CANCEL')
    , (7, 'C', '2015-09-05', 'ACTIVE')
    , (6, 'A', '2015-09-01', 'CANCEL')
    , (5, 'B', '2015-08-29', 'INPROGRESS') 
    , (4, 'C', '2015-08-29', 'INPROGRESS')
    , (3, 'B', '2015-08-12', 'CANCEL')
    , (2, 'B', '2015-08-03', 'ACTIVE')
    , (1, 'A', '2015-06-01', 'ACTIVE');

Output:

ParRecId    START       END         STATUS
A           2015-09-01  NULL        OPEN
B           2015-08-12  2015-08-29  CLOSED
C           2015-09-12  2015-09-19  CLOSED
C           2015-10-19  NULL        OPEN
E           2015-11-25  NULL        OPEN

Best Answer

Related Solutions

Sql-server – Can’t retrieve data of countries and regions

Sql-server – Help to retrieve data set

Related Question