Relational Table & SQL Challenge 
Author Message
 Relational Table & SQL Challenge

Check out Celko's posts for his Nested Sets solution, and also the following
links:

http://www.*-*-*.com/
http://www.*-*-*.com/

--
BG
SQL Server MVP
http://www.*-*-*.com/



Quote:
> Hi,

> My problem is better explained in the attached *.txt file. Please set
> wordwrap = true so the display is correct.

> Bye.



Fri, 10 Dec 2004 16:56:26 GMT  
 Relational Table & SQL Challenge
Abdul,

  Here is an example that shows a depth-first search of a tree in T-SQL.  It
will not work if the recusion limit of SQL is exceeded.

create table Victor (
  cod char(4),
  coden char(4)
)

insert into Victor values ('I018','I029')
insert into Victor values ('I018','I022')
insert into Victor values ('I022','I021')
insert into Victor values ('I022','I087')
insert into Victor values ('I087','I011')
insert into Victor values ('I087','I012')
go

create function fVictor (

) returns varchar(1000) as begin
  return ''
end
go

alter function fVictor (

) returns varchar(1000) as begin




  select coden from Victor







  end



end
go

select dbo.fVictor('I018')
select dbo.fVictor('I087')
select dbo.fVictor('I022')
go
drop function fVictor
drop table Victor

Steve Kass
Drew University

Quote:

> Hi,

> My problem is better explained in the attached *.txt file. Please set
> wordwrap = true so the display is correct.

> Bye.

> Hi techno-wizards,

> Allright, I have a self-relational table and the data is something like this:

> =======================
> UserID    |   ParentID
> =======================
>     1           0
>     2           1
>     3           1
>     4           2
>     5           2
>     6           3
>     7           3
>     8           4
>     9           4
> =======================

> I think you can understand the situation where ParentID is related to UserID.
> For example, UserID = 5 has a parent where UserID = 2. This table simulate
> a hirarchical tree display:

>                 (0)
>                  |
>                 (1)
>                 / \
>                /   \
>              (2)   (3)
>              / \   / \
>             (4)(5)(6)(7)
>            /  \
>           (8) (9)

> So, the problem is, how can I write the SQL statement that will return (i.e)
> UserID = 2 and all the children beneath it. Result example:

> =======================
> UserID    |   ParentID
> =======================
>     2           1
>     4           2
>     5           2
>     8           4
>     9           4
> =======================

> Or as depicted in the tree:

>              (2)
>              / \
>             (4)(5)
>            /  \
>           (8) (9)

> I think this problem is quite popular sometimes ago but I never came across
> anobody's solution. So, can anybody solve this, or at least recommend me
> a web site.

> Thanx in advance.



Fri, 10 Dec 2004 17:04:50 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. FPW 2.6 & Security Key Memory Challenge

2. Challenge for SQL Masters only!

3. Challenge for SQL Masters only!

4. sql challenge

5. SQL Query in a relational database

6. FoxBase vs. SQL Relational Database

7. Help please, RQBE Relational Table Problem

8. SQL Select & Table Buffering

9. TRYING TO UPDATE sql TABLES WITH FOX TABLES

10. Programmatically ALTERing TABLE structure of SQL table from VFP

11. Fox Table to SQL table

12. Update SQL 7 table from vpf6 table

 

 
Powered by phpBB® Forum Software