recursive-query

Building a Table Dependency Graph With A Recurssive Query

I am trying to build a dependency graph of tables based on the foreign keys between them. This graph needs to start with an arbitrary table name as its root. I could, given a table name look up the tables that reference it using the all_constraints view, then look up the tables that reference them, and so on, but this would be horrible...

Help with recursive query

Hi, I've following problem, which I've not been able to do successfully. Your help will be appreciated. I'm using SQL 2005, and trying to do this using CTE. Table has following 2 columns DocNum DocEntry 1 234 2 324 2 746 3 876 3 764 4 100 4 3...

Recursive Query using HQL

Hi every one, I have this Table CREATE TABLE IF NOT EXISTS `branch` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studcount` int(11) DEFAULT NULL, `username` varchar(64) NOT NULL, `branch_fk` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FKADAF25A2A445F1AF` (`branch_fk`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=14...

Recursive function in sql server 2005?

Can anybody suggest programming examples that illustrate recursive functions? For example fibonacci series or factorial.. ...

Make a recursive function in SQL Server 2005

cat_id prod_name parent_cat_id ------ ---------- ------------ 1 prod_1 2 2 prod_2 5 3 prod_3 1 4 prod_4 3 5 prod_5 7 6 prod_6 5 In a recursive function, make a table and by using these, if cat_id = 1 and parent_cat_id = 1 take that product name and if that product category id and parent category id is sam...

Sql Recursive query to identify relation between 2 users of family tree

Users Data UserId DisplayName 12 Rahul 13 Anjali 14 Faruk 15 Shabina 16 Shakira 17 Ali 18 Ronak 19 Dali 20 Bali 21 Kali 22 Katrina 23 Sita 24 Gita 25 Ram 26 Shyam 27 Suhana 28 Suhas 29 Raj 30 Taslim 31 Ritik 32 Tejas 33 Dipika 34 Bush 35 Dyna 36 Bushiar 37 Salman 38 Ruksana 39 Khushi 40 Tazz 41 Miki 42...

How to write a recursive query in SQL Server 2000

I have a table which has a list which looks like this References R. Name LineNo. References A 1.1 (B,24.1) A 6.3 (A, 1.3), (D, 22.1) B 23.1 (A. 1.2) B 24.1 (B,23.1) C 2 (A, 1.1) D 3.12 (A, 6.3) The query sho...

Recursive XML in scala

Hello Everyone, I am trying to parse this document in scala: <?xml version="1.0"?> <model> <joint name="pelvis"> <joint name="lleg"> <joint name="lfoot"/> </joint> <joint name="rleg"> <joint name="rfoot"/> </joint> </joint> </model> I want...

Oracle - Recursive query is not recursing (not as much as I'd like)

I had an idea that I could write a query to find all the decendent tables of a root table, based on foreign keys. Query looks like this: select level, lpad(' ', 2 * (level - 1)) || uc.table_name as "TABLE", uc.constraint_name, uc.r_constraint_name from all_constraints uc where uc.constraint_type in ('R', 'P') start with uc.table_name ...

sql to calculate daily totals minues the previous day's totals

I have a table that has a date, item, and quantity. I need a sql query to return the totals per day, but the total is the quantity minus the previous day totals. The quantity accumulates as the month goes on. So the 1st could have 5 the 2nd have 12 and the 3rd has 20. So the 1st adds 5 2nd adds 7 to make 12 3rd adds 8 to make 20. I'v...

SQL select inverted tree

Hello i have tree structure in sql. Logics is standard: SomeID, ParentID, other fields. I have select procedure, which selects data like this: 1. 1.1 1.1.1 and so on. How to write the select procedure, to get the inverted result(first are selected the deepest branches, last - root branches), like this: 1.1.1. 1.1. 1. 2.2.2.2.2. 2.2....

Mysql column's row starts with value of another row

Hello all, I have a table like below: MyTable -------- A B C A,B,C are columns and MyTable is the table name and i want run a mysql query like: SELECT MT1.A, MT2.A, MT2.B FROM MyTable MT1, MyTable MT2 WHERE MT1.B<>MT2.B and MT2.B like "MT1.B%" and MT2.status=0; As you see from the query above i have a table and i wa...

Best method to search hierarchical data

I'm looking at building a facility which allows querying for data with hierarchical filtering. I have a few ideas how I'm going to go about it but was wondering if there are any recommendations or suggestions that might be more efficient. As an example imagine that a user is searching for a job. The job areas would be as follows. 1: Sc...

Apply a recursive CTE on grouped table rows (SQL server 2005).

Hi all, I have a table (ROOMUSAGE) containing the times people check in and out of rooms grouped by PERSONKEY and ROOMKEY. It looks like this: PERSONKEY | ROOMKEY | CHECKIN | CHECKOUT | ROW ---------------------------------------------------------------- 1 | 8 | 13-4-2010 10:00 | 13-4-2010 11:00 | 1 1...

SQL select descendants of a row

Suppose a tree structure is implemented in SQL like this: CREATE TABLE nodes ( id INTEGER PRIMARY KEY, parent INTEGER -- references nodes(id) ); Although cycles can be created in this representation, let's assume we never let that happen. The table will only store a collection of roots (records where parent is null) and their...

find relationships in a many-to-many structure with sql

Hi, my question is near a parent-child problem, and may need some recursive query, but i didn't find any answers by browsing forums. here is my problem: I've got 3 tables: T1 (people) T2 (places) T3 (relationship betwenn A and B) ------- ------ -------- id1 (pk) id2 (pk) id3 (pk) name city ...

Aggregate survey results recursively by manager

I have a StaffLookup table which looks like this. UserSrn | UserName | ManagerSrn =============================== ABC1 | Jerome | NULL ABC2 | Joe | ABC1 ABC3 | Paul | ABC2 ABC4 | Jack | ABC3 ABC5 | Daniel | ABC3 ABC6 | David | ABC2 ABC7 | Ian | ABC6 ABC8 | Helen | ABC6 The staff stru...

Recursive query question - break rows into columns?

I have a table "Families", like so FamilyID PersonID Relationship ----------------------------------------------- F001 P001 Son F001 P002 Daughter F001 P003 Father F001 P004 Mother F002 P005 Daughter F002 P006 Mother F003 P007 Son F00...

How does a Recursive CTE run, line by line?

I think I've got the format of Recursive CTEs down well enough to write one, but still find myself frustrated to no end that I cannot manually process one (pretend to be the SQL engine myself and reach the result set with pen and paper). I've found this, which is close to what I'm looking for, but not detailed enough. I have no problem t...

one to many join on 3 levels all on the same table

I have this table with pages, these pages have parents that are also pages in the same table. For this examples sake the table looks like: table: Pages PageId :Key PageParent :Foreign Key PageName Now my question is what would the SQL look like when creating a menustructure like: PageId PageParent PageName 1 NULL ...