Return Parent Records with Child Records Equaling Specific Values and Where Total Set of Child Records for a Given Parent Equal a Specific Value

Find SUM of child table column is less than parent child total column

I think you should use Group by for your requirement, and can't use Alias in Where condition in your case. (I'm using SQL Server, not sure if you have different tools)
I made some changes like that

SELECT 
    SUM(b.amount) AS sum_transection,
    a.amount AS total,
    SUM(b.amount) - a.amount AS diff
FROM 
    dbo.tb1 a INNER JOIN  dbo.tb2 b ON
    a.grp = b.grp AND a.head = b.head 
GROUP BY a.grp, a.amount
having SUM(b.amount) - a.amount >0

Child Records getting skipped because of pagination in SQL

Why not calculating the amount of each parent with the same query?

DECLARE @DataSource TABLE
(
    [RowNumber] INT
   ,[ParentTxnNumber] INT
   ,[ChildTxnNumber] INT
   ,[Amount] INT
);

INSERT INTO @DataSource ([RowNumber], [ParentTxnNumber], [ChildTxnNumber], [Amount])
VALUES (1, 50, 101, 500)
      ,(2, 50, 102, 500)
      ,(3, 50, 103, 500)
      ,(4, 51, 104, 510)
      ,(5, 51, 105, 520)
      ,(6, 51, 106, 530)
      ,(7, 52, 107, 500)
      ,(8, 52, 108, 500)
      ,(9, 52, 109, 500)
      ,(10, 52, 110, 500);

DECLARE @PageSize INT = 5
       ,@PageNum INT = 1;

select * 
      ,SUM([Amount]) OVER (PARTITION BY [ParentTxnNumber]) AS [AmountPerPArent]
from @DataSource 
ORDER BY [RowNumber] OFFSET (@PageSize * (@PageNum - 1)) ROWS FETCH NEXT @PageSize ROWS ONLY;

---

SELECT TOP (@PageSize) WITH TIES *
FROM
(
    select *
          ,DENSE_RANK() OVER(ORDER BY [ParentTxnNumber]) AS [GroupID]
    from @DataSource 
    ORDER BY DENSE_RANK() OVER(ORDER BY [ParentTxnNumber])
    OFFSET (@PageSize * (@PageNum - 1)) ROWS --FETCH NEXT @PageSize ROWS ONLY;
) DS
ORDER BY [GroupID]

How to return instances where all members of GROUP BY equal a certain value?

Using conditional aggregation:

SELECT AssetID
FROM rdInvestments
GROUP BY AssetID
HAVING SUM(IIF(LeadIndication <> 'FALSE', 1, 0)) = 0;

Another way:

SELECT AssetID
FROM rdInvestments
GROUP BY AssetID
HAVING SUM(IIF(LeadIndication = 'FALSE', 1, 0)) = COUNT(*);

SQL query to identify patterns in Parent-Child relationship

The following will produce the results you want, assuming you rename your TYPE fields so they're distinct from one another - one should probably be FACILITY_TYPE and the other should apparently be LOAN_TYPE:

WITH cteDistinct_facility_loan_types
       AS (SELECT DISTINCT FACILITY_NUM,
                  LOAN_TYPE
             FROM YOUR_TABLE),
     cteFacility_loan_types
       AS (SELECT FACILITY_NUM,
                  LISTAGG(LOAN_TYPE, ',')
                    WITHIN GROUP (ORDER BY FACILITY_NUM) AS FACILITY_LOAN_TYPES
             FROM cteDistinct_facility_loan_types
             GROUP BY FACILITY_NUM)
SELECT t.*,
       t.FACILITY_TYPE || '-' || flt.FACILITY_LOAN_TYPES AS PATTERN
  FROM YOUR_TABLE t
  INNER JOIN cteFacility_loan_types flt
    ON flt.FACILITY_NUM = t.FACILITY_NUM
  ORDER BY t.FACILITY_NUM

db<>fiddle here

Given a recursive query that starts with a child, how can I eliminate sibling and parent rows?

Here is an answer that uses a while loop instead.

Solution 1, using while loop and a temp table

/* Populating the temp table with the data */
DECLARE @recurse TABLE
(projectId INT,parent int);

INSERT INTO @recurse (projectId,parent) VALUES (1,-1);
INSERT INTO @recurse (projectId,parent) VALUES (2,-0);
INSERT INTO @recurse (projectId,parent) VALUES (3,2);
INSERT INTO @recurse (projectId,parent) VALUES (4,2);
INSERT INTO @recurse (projectId,parent) VALUES (5,-1);
INSERT INTO @recurse (projectId,parent) VALUES (6,3);
INSERT INTO @recurse (projectId,parent) VALUES (7,6);

DECLARE @recurse2 TABLE
(projectId INT,parent INT);

--Start by inserting the root element
INSERT INTO @recurse2 ( projectId, parent)
SELECT * FROM @recurse WHERE projectId = 2

--insert elements until all children have all children
WHILE EXISTS (SELECT * FROM @recurse WHERE parent IN (SELECT projectId FROM @recurse2) AND projectId NOT IN (SELECT projectId FROM @recurse2) )
BEGIN
    INSERT INTO @recurse2
    (
        projectId,
        parent
    )
    SELECT * FROM @recurse WHERE parent IN (SELECT projectId FROM @recurse2) AND projectId NOT IN (SELECT projectId FROM @recurse2)
END 

SELECT * FROM @recurse2

Solution 2
For performance you can create a intermidate table with the result from the while loop. This intermidiate table can be updated on an intervall, or as part of the creating an element in the main table. This could be either a part of your business logic or as a DB trigger.

Here is the code I would write if you wanted this as a scheduled job:

-- Populating the temp table with the data 
DECLARE @recurse TABLE
(projectId INT,parent int);

INSERT INTO @recurse (projectId,parent) VALUES (1,-1);
INSERT INTO @recurse (projectId,parent) VALUES (2,-0);
INSERT INTO @recurse (projectId,parent) VALUES (3,2);
INSERT INTO @recurse (projectId,parent) VALUES (4,2);
INSERT INTO @recurse (projectId,parent) VALUES (5,-1);
INSERT INTO @recurse (projectId,parent) VALUES (6,3);
INSERT INTO @recurse (projectId,parent) VALUES (7,6);

DECLARE @recurse2 TABLE
(projectId INT,parent INT, lvl int);

--Start by inserting all elements root at lvl 0
INSERT INTO @recurse2 ( projectId, parent, lvl ) SELECT projectId, parent, 0 FROM @recurse 
SELECT * FROM @recurse2

--insert elements until we have all parents for all elements
WHILE EXISTS (SELECT * FROM @recurse2 a WHERE lvl IN (SELECT TOP 1 lvl FROM @recurse2 b WHERE a.projectId = b.projectId ORDER BY lvl DESC) AND a.parent > 0 )
BEGIN
    --Insert the next parent for all elements that does not have a their top level parent allready
    INSERT INTO @recurse2 ( projectId, parent , lvl )
    SELECT  projectId, 
            (SELECT b.parent FROM @recurse b WHERE b.projectId = a.parent), 
            lvl + 1 
    FROM @recurse2 a WHERE lvl IN (SELECT TOP 1 lvl FROM @recurse2 b WHERE a.projectId = b.projectId ORDER BY lvl DESC) AND a.parent > 0 
END 

--Find all children to an element
SELECT * FROM @recurse2 WHERE parent = 2

The big advantage of solution #2 is that the performance should be REALY good for reads, probably even better than CTE's. Also it works equally well for reading from bottom to top, as top to bottom.

find all parent records where all child records have a given value (but not just some child records)

If I get it right your problem can be classified as relational division. There are basically two ways to approach it:

1a) Forall x : p(x)

which in SQL has to be translated to:

1b) NOT Exists x : NOT p(x)

For your problem that would be something like:

SELECT e.* 
FROM events e
WHERE NOT EXISTS (
    SELECT 1 
    FROM PARTICIPANTS p
    WHERE p.status <> 'present'
      AND p.event_id = e.event_id
)

i.e. any given event where there does not exist a participant such that status != 'present'

The other principle way of doing it is to compare the number of participants with the number of participants with status present

SELECT e.id 
FROM events e
JOIN participants p 
    ON p.event_id = e.id 
GROUP BY e.event_id 
HAVING count(*) = count( CASE WHEN p.status = 'present' then 1 end )

Both solutions are untested so there might be errors in there, but it should give you a start

Category and Subcategory display (Parent/Child)

A recursive CTE can do this - down to any depth:

EDIT: added proper sorting and added one element third level "Test Cricket2" below ID 4

DECLARE @tbl TABLE(SportID INT,SportName VARCHAR(100),ParentID INT);
INSERT INTO @tbl VALUES
 (1,'Cricket',0)
,(2,'T20 Cricket',1)
,(3,'50 Over Cricket',1)
,(4,'Test Cricket',1)
,(5,'Football',0)
,(6,'World cup football',5)
,(7,'Euro Cup Football',5)
,(11,'Test Cricket2',4)
,(8,'Volleyball',0)
,(9,'Beach Volleyball',8)
,(10,'Ground Volleyball',8);

WITH RecursiveCTE AS
(
    SELECT CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS VARCHAR(MAX)) AS Inx, SportID,SportName,ParentID,1 AS Level
    FROM @tbl 
    WHERE ParentID=0
    UNION ALL
    SELECT rc.Inx + '.' +CAST(ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) AS VARCHAR(10)),tbl.SportID,CAST(REPLICATE('--',rc.Level) + tbl.SportName AS VARCHAR(100)),tbl.ParentID,rc.Level +1 
    FROM RecursiveCTE AS rc
    INNER JOIN @tbl AS tbl ON tbl.ParentID=rc.SportID
)
SELECT * FROM RecursiveCTE
ORDER BY Inx,Level

The result

+-------+---------+----------------------+----------+-------+
| Inx   | SportID | SportName            | ParentID | Level |
+-------+---------+----------------------+----------+-------+
| 1     | 1       | Cricket              | 0        | 1     |
+-------+---------+----------------------+----------+-------+
| 1.1   | 2       | --T20 Cricket        | 1        | 2     |
+-------+---------+----------------------+----------+-------+
| 1.2   | 3       | --50 Over Cricket    | 1        | 2     |
+-------+---------+----------------------+----------+-------+
| 1.3   | 4       | --Test Cricket       | 1        | 2     |
+-------+---------+----------------------+----------+-------+
| 1.3.1 | 11      | ----Test Cricket2    | 4        | 3     |
+-------+---------+----------------------+----------+-------+
| 2     | 5       | Football             | 0        | 1     |
+-------+---------+----------------------+----------+-------+
| 2.1   | 6       | --World cup football | 5        | 2     |
+-------+---------+----------------------+----------+-------+
| 2.2   | 7       | --Euro Cup Football  | 5        | 2     |
+-------+---------+----------------------+----------+-------+
| 3     | 8       | Volleyball           | 0        | 1     |
+-------+---------+----------------------+----------+-------+
| 3.1   | 9       | --Beach Volleyball   | 8        | 2     |
+-------+---------+----------------------+----------+-------+
| 3.2   | 10      | --Ground Volleyball  | 8        | 2     |
+-------+---------+----------------------+----------+-------+

---

Related Topics