I have the following `EventTimes` table: ``` DROP TABLE IF EXISTS dbo.EventTimes; CREATE TABLE dbo.EventTimes ( EventTimeKeyId INT IDENTITY(1,1) PRIMARY KEY, EventId INT NOT NULL, StartTime TIME NOT NULL, EndTime TIME NOT NULL ); ``` With the following data: ``` -- Event 1 INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime) VALUES (1, '04:00:00', '14:00:00'), (1, '06:00:00', '11:00:00'), (1, '09:00:00', '12:00:00'), (1, '13:00:00', '14:00:00'), -- Gap between this row and the next row (1, '02:30:00', '04:00:00'); -- Notice the half-hour on this one -- Event 2 INSERT INTO dbo.EventTimes (EventId, StartTime, EndTime) VALUES (2, '00:00:00', '06:00:00'), -- Gap between this row and the next row (2, '09:00:00', '13:00:00'), (2, '11:00:00', '15:00:00'); ``` Notice: - The same `Event` can have two time ranges that overlap each other. E.g. same `Event` from 4 AM to 2 PM and and also from 6 AM to 11 AM. - There can also be gaps between the two time ranges. E.g. same `Event` from 1 PM to 2 PM and also from 2:30 PM to 4 PM. End Goal: I'm trying to calculate the `TotalTime` of a given `Event` ignoring the duplicate overlapping time. E.g. for the set of ranges of 9 AM to 1 PM and 11 AM to 3 PM, the distinct `TotalTime` should be 6 hours (9 AM to 3 PM). Conversely, I also don't want to count the time in the gaps between two time ranges. So for the set of ranges of 1 PM to 2 PM and 2:30 PM to 4 PM the `TotalTime` should be 2.5 hours. (Note these are just subsets of the full example above, and the final result should be the sum of all of these unique times per `Event`.) The `TotalTime` should never exceed 24 hours, these times are all within a single day (as is the `TIME` data type). Expected final results for the provided examples in the scripts above: dbfiddle.uk for reference. Other Info: - If it's easier to work with datetimes, feel free to alter the data types from `TIME` to `DATETIME`. I can convert the results back, no proble

I have some relations embodying a directed acyclic graph that includes patterns similar to the following: I'm looking for an efficient way to traverse this graph data. Here is an example of the seemingly simple task of counting descendants of node 0: db<>fiddle ``` DROP TABLE IF EXISTS #edges; CREATE TABLE #edges(tail int, head int); INSERT INTO #edges(tail,head) VALUES (0,1), (5, 6), (10,11), (15,16), (0,2), (5, 7), (10,12), (15,17), (1,2), (6, 7), (11,12), (16,17), (1,3), (7, 8), (11,13), (17,18), (2,3), (7, 9), (12,13), (17,19), (2,4), (8, 9), (12,14), (18,19), (3,4), (8,10), (13,14), (3,5), (9,10), (13,15), (4,5), (9,11), (14,15), (4,6), (14,16); WITH descendents(node) AS( SELECT 0 as node UNION ALL SELECT head as node FROM descendents as prior JOIN #edges ON prior.node = tail ) SELECT (SELECT COUNT(node) FROM descendents) as total_nodes, (SELECT COUNT(node) FROM (SELECT DISTINCT node FROM descendents) as d) as distinct_nodes ``` This results in the following: ``` total_nodes | distinct_nodes 10512 | 20 ``` Every path is visited instead of each node once `total_nodes` seems to grow at about 2^n where n is the number of nodes in the example. This is because every possible path is traversed rather than each node once. This n=29 example results in 1,305,729 `total_nodes` and took 75 seconds to complete on my local instance of SQL Server Express. The obvious strategy is to only visit previously unvisited nodes on each iteration. Excluding redundant additions using `WHERE...NOT IN` does not seem to be supported The most direct method of preventing visits to previously visited nodes would seem to be to filter right in the recursive member of the CTE as follows: ``` SELECT head as node FROM descendents as prior JOIN #edges ON prior.node = tail WHERE head NOT IN (SELECT node from descendents) ``` This produces the error "Recursive member

TL;DR; Is it a bug that SQL Server allows a scalar UDF to recursively call itself when schema-bound, but only when altered to do so using the `CREATE OR ALTER` syntax? Or is it a bug that other syntaxes are disallowed? A trivial recursive scalar UDF can be constructed as follows ``` CREATE FUNCTION dbo.Try1 (@i int) RETURNS int AS BEGIN RETURN IIF(@i = 0, 0, @i + dbo.Try1(@i - 1)); END; ``` As long as this is not schema-bound then this is allowed. Let's try schema-binding it, we'll do ``` CREATE FUNCTION dbo.Try2 (@i int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN IIF(@i = 0, 0, @i + dbo.Try2(@i - 1)); END; ``` Nope. ``` Cannot find either column "dbo" or the user-defined function or aggregate "dbo.Try2", or the name is ambiguous ``` Create it without recursion then alter it ``` CREATE OR ALTER FUNCTION dbo.Try3 (@i int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN NULL; END; ``` ``` ALTER FUNCTION dbo.Try3 (@i int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN IIF(@i = 0, 0, @i + dbo.Try3(@i - 1)); END; ``` A different error this time: ``` Cannot schema bind function 'dbo.Try3' because name 'dbo.Try3' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself. ``` Hmmm, `an object cannot reference itself` who invented that rule? It's not in the docs. Let's try with `CREATE OR ALTER` ``` CREATE OR ALTER FUNCTION dbo.Try4 (@i int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN IIF(@i = 0, 0, @i + dbo.Try4(@i - 1)); END; ``` Still not. But if we first do `CREATE` without recursion, then do `CREATE OR ALTER` (not `ALTER`) with recursion then it works ``` CREATE OR ALTER FUNCTION dbo.Try5 (@i int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN NULL; END; ``` ``` CREATE OR ALTER FUNCTION dbo.Try5 (@i int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN IIF(@i = 0, 0, @i + dbo.Try5(@i - 1)); END; ``` The weird thing is: this doesn't make sense whichever way `CREATE OR ALTER` works underneath. I

``` CREATE TABLE t1 ( COL1 nvarchar(36), COL2 nvarchar(36), ExpectedResult nvarchar(36) ) INSERT INTO t1 (COL1, COL2, ExpectedResult) VALUES ('CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'), ('70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A','CA4462D3-BD2C-4C80-A50E-586DA28A877A'), ('A3DEBD5A-C8DD-494E-B2CC-97D693677071','70CA5891-F6D0-40BA-A6FC-2DA8A035F983','CA4462D3-BD2C-4C80-A50E-586DA28A877A'), ('CBC648CE-B98C-4831-B6E2-FBF437BD012B','A3DEBD5A-C8DD-494E-B2CC-97D693677071','CA4462D3-BD2C-4C80-A50E-586DA28A877A'); ``` https://dbfiddle.uk/a-EAQ5YV Above is the structure of my table. What I want to do is: - From starting point 1, match id "A3DEBD6Axxxxxxxx" from COL2 with its occurrence in COL1 (step2) - When the match is found, check the corresponding value in COL2 AND check if the corresponding value also occurs in COL1. If yes, continue searching until no match. - Take the "parent" ID from COL1 (CA4462D3xxxxxxx) and write it in the column 'Expected Result' for each row concerned by this ID. I have tried some self-joins on the table but I'm very far from the expected result. Any idea of how this can be achieved?

Good afternoon everyone I found just one post here within the last year about this, but it doesn't help my situation. I have been working with MySQL and trying to improve my knowledge of recursive CTE. The version of MySQL is 8.0.19 on a Windows device. The table that I have is generated with: ``` DROP TABLE IF EXISTS test_table; CREATE TABLE test_table ( `id` int(5) NOT NULL AUTO_INCREMENT, `source` varchar(20) NOT NULL, `destination` varchar(20) NOT NULL, `route` varchar (200) NOT NULL, `open` BOOLEAN DEFAULT true, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; ``` Values are entered using: ``` INSERT INTO test_table VALUES (1, 'STA_A', 'STA_B', 'Line1', true), (2, 'STA_B', 'STA_A', 'Line1', true), (3, 'STA_B', 'STA_C', 'Line1', true), (4, 'STA_C', 'STA_B', 'Line1', true), (5, 'STA_C', 'STA_D', 'Line1', true), (6, 'STA_D', 'STA_C', 'Line1', true), (7, 'STA_D', 'STA_E', 'Line1', true), (8, 'STA_E', 'STA_D', 'Line1', true), (9, 'STA_K', 'STA_B', 'Line2', true), (10, 'STA_B', 'STA_K', 'Line2', true), (11, 'STA_B', 'STA_L', 'Line2', true), (12, 'STA_L', 'STA_B', 'Line2', true), (13, 'STA_L', 'STA_M', 'Line2', true), (14, 'STA_M', 'STA_L', 'Line2', true), (15, 'STA_M', 'STA_N', 'Line2', true), (16, 'STA_N', 'STA_M', 'Line2', true); ``` Finally, here is the recursive CTE: ``` SET profiling = 1; SET @from = 'STA_A'; SET @to = 'STA_M'; SET @via = 'STA_M'; SET @avoid = 'XXXXX'; WITH RECURSIVE cte AS ( -- Anchor SELECT test_table.destination, CONCAT(test_table.source, ' => ', test_table.route, ' => ', test_table.destination) path, 1 length FROM test_table WHERE test_table.source = @from UNION ALL -- Recursive member SELECT test_table.destination, CONCAT(cte.path, ' => ', test_table.route, ' => ', test_table.destination) path, cte.length + 1 length FROM cte INNER JOIN test_table ON test_table.source = cte.destination WHERE NOT FIND_IN_SET(test_table.destination,

When a database has a dependency table showing the parent-child relationship of items... I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero or null, and this is the indicator that this item is a top level item aka the root of the dependency tree. This is what I'm seeing used in a recursive CTE as the anchor member. However, it seems that requiring this zero or null field might be unnecessary, since the parent item can be deduced by seeing that a given item number appears in the dependency table as a "parent id" but never as a "child id". Example of deducing item 300 as the root parent of the 300-330 tree: Dependency table Parent_id child_id 300 310 310 320 320 330 Example of the (far more common in examples) use of `parent_id` = 0 to define the root parent: Parent_id child_id 0 300 300 310 310 320 320 330 So the question here is one of "best practices". If I have the ability to design a dependency table from the ground up, is there a reason to go with one structure vs the other?

I have a recursive query that takes too long - 30+ ms where doing the individual queries to extract the same data manually takes `create table subjects ( subject_id bigint not null constraint pk_subjects primary key ); create table subject_group_members ( subject_group_id bigint not null constraint fk_subject_group_members_subject_group_id_subjects_subject_id references subjects(subject_id) on delete cascade, subject_id bigint not null constraint fk_subject_group_members_subject_id_subjects_subject_id references subjects(subject_id) on delete cascade, constraint pk_subject_group_members primary key (subject_group_id, subject_id) ); create index idx_subject_group_members_subject_id on subject_group_members (subject_id); create index idx_subject_group_members_subject_group_id on subject_group_members (subject_group_id); ` Data might look like this: subject_group_id subject_id 1 2 1 3 1 4 2 5 3 5 I want to know all the groups that 5 is a member of (1 by inheritance, 2 & 3 directly, not 4 or any other subject ids). This query works as expected: `with recursive flat_members(subject_group_id, subject_id) as ( select subject_group_id, subject_id from subject_group_members gm union select flat_members.subject_group_id as subject_group_id, subject_group_members.subject_id as subject_id from subject_group_members join flat_members on flat_members.subject_id = subject_group_members.subject_group_id ) select * from flat_members where subject_id = 5 ` But run with real data I get this query plan: ``` CTE Scan on flat_members (cost=36759729.47..59962757.76 rows=5156229 width=16) (actual time=26.526..55.166 rows=3 loops=1) Filter: (subject_id = 30459) Rows Removed by Filter: 48984 CTE flat_members -> Recursive Union (cost=0.00..36759729.47 rows=1031245702 width=16) (actual ti

Given an input like "ABC" generate a query that calculates all potential splits of 0 or more of the given string, Desired output, ``` A B C A BC AB C ABC ``` Given an input like "ABCD" ``` A B C D A BC D A B CD AB C D A BCD AB CD ABC D ABCD ``` Not all that concerned with how output is formed, array, rows, json, etc. More looking for discrete list of all permutations of grouping.

Consider the following table and data (fiddle available here): ``` CREATE TABLE test ( id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, next_id INTEGER NOT NULL ); INSERT INTO test (id, next_id) VALUES (1, 3), (2, 3), (3, 6), (4, 5), (5, 6), (6, 8), (7, 10), (8, 9); ``` I want to be able to select only the rows starting from ID = 1 following the Next ID in a recursive way. That is, I want to select in this example the following rows: ID Next ID 1 3 3 6 6 8 8 9 I realize it would be relatively easy with a programming language to achieve, but I'd like to know if anyone has an idea how to achieve that using pure SQL.

I'm trying to generate all combinations of a list of Strings `list = ['A', 'B', 'C', 'D']`. I want to generate all possibilities and then search in the DB `ABCD, ABC, ABD, ACD, BCD, AB, AC, AD, BC, BD, CD, A, B, C, D` With my columns being like this: result code 1 A 2 BC 3 AC 4 B 5 ABC 6 AD 7 BCD 8 CD 9 ABCD 10 ABD ....... ...... To clarify: Order doesn't matter (ABC = BAC = CAB) and it should return multiple results (For list = ['A', 'B', 'C'] with same with the 10 columns above it should return [1, 2, 3, 4, 5]). I'm using Postgres and I've tried with some recursive functions I've seen but none quite accomplish my needs.