Binary trees needed? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Binary trees needed?

Author

Topic

glock
Starting Member

8 Posts

Posted - 2003-06-17 : 09:24:34

Hi all,
There are two questions that I want to ask you guys and I would appreciate your help on this.
I have a set of tables in a relational database. For example,

Person table

PersonID, Surname, Forename etc..

Employee Table

EmployeeID, PersonID, NationalIDNumber, DateJoinedCompany

The relationship between the two tables is pretty obvious. The two tables join on the PersonID. If we introduce a further relationship to the above so that we now have :

Divorce

DivorceID, PersonID, DateOfDivorce

And,

Earmarked

DivorceID, ReceipientOfOrder

Again, we can see that the Divorce table links to the Person table and the Earmarked table joins with the Divorce table. I hope you are all happy with this extremely simplified version of a relational database.
I am currently writing an SP that would take a person NationalIDNumber and return to me the tables where there is data that belongs to this member.
So something like,

DECLARE @PersonID INTEGER
SET @PersonID = (Select PersonID from Person where NationalIDNumber = ‘XXXYYY123’)

Once we have our PersonID, we can then search our Employee table for the PersonID and we can do the same for Divorce. The problem is, the Earmarked table does not directly links to the Person table so how do you do it?

I thought of implementing a Binary tree structure, but I am not sure how to approach the whole concept. Any suggestion son how to tackle this kind of problems?

dsdeming

479 Posts

Posted - 2003-06-17 : 09:51:02

This assumes that there are person/employee rows for which no corresponding row exists in Divorce or Earmarked.

SELECT p.Surname,
p.Forename,
e.EmployeeID,
e.DateJoinedCompany,
d.DivorceID,
d.DateOfDivorce,
m.RecipientOfOrder
FROM Person p
JOIN Employee e ON p.PersonID = e.PersonID
LEFT OUTER JOIN Divorce d ON p.PersonID = d.PersonID
LEFT OUTER JOIN Earmarked m ON d.DivorceID = m.DivorceID
WHERE e.NationalIDNumber = @value

Dennis

glock
Starting Member

8 Posts

Posted - 2003-06-17 : 10:01:44

Denis,

Thanks for your input.
I don't think I a explained my problem very well to be honest. Your solution kind of works for my original explanation of the problem but it doesn’t actually solve the problem I have.

As I said in my original post. The relational database I am working with is more complex than my example. In fact, there 418 tables involved!

What I am attempting to do is to be able to navigate the database and pick up the tables which have records for the person I want.

So for example, I know that the person table (having PersonID as the primary key) links to the tables : Address, Notes, Employee, Divorce etc.
So if Mr Bloggs has a record in those tables then by entering his ID number, the SP should return those tables.
The problem is, how does the SP know that while the Peron table links to the table Divorce, how does it know that Divorce links to Earmarked?
In other words, Mr Bloggs has a record in Person, a record in Divorce and a Record in Earmarked.
Person links to Divorce and Divorce links to Earmarked etc..

Hope this explains my position.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-06-17 : 18:51:13

quote:
What I am attempting to do is to be able to navigate the database and pick up the tables which have records for the person I want.

Take a look at this article:

http://www.sqlteam.com/item.asp?ItemID=13122

And look in Books Online under the "INFORMATION_SCHEMA" views, there is a view that lists the foreign key relationships between tables. You might be able to write something that can auto-generate the SQL you need to search all the related tables, but it is going to be pretty difficult (and if you don't have foreign keys defined, then you're SOL)

I know you don't want to hear this, but having the computer try to figure out how tables are joined is kinda backwards. The person querying the database should know how they relate, and write queries that use those relationships. The computer does not know these things unless an operator tells it so. If you're not familiar enough with the database structure to do so, study it until you are.

SQL Server's diagram feature can help get you started on deterimining how each table relates to another, you can access it through Enterprise Manager under the "Diagrams" section of the database.

glock
Starting Member

8 Posts

Posted - 2003-06-18 : 04:37:28

quote:

I know you don't want to hear this, but having the computer try to figure out how tables are joined is kinda backwards. The person querying the database should know how they relate, and write queries that use those relationships. The computer does not know these things unless an operator tells it so. If you're not familiar enough with the database structure to do so, study it until you are.

I have to say that I do not completely understand the relationship of the 418 tables. If I did, I would be earning more money! But my idea is to develop a tool that would take a NationalIDNumber and search the database for records that belong to the specific person.

quote:

SQL Server's diagram feature can help get you started on deterimining how each table relates to another, you can access it through Enterprise Manager under the "Diagrams" section of the database.

That is something I have been playing around with lattely but it doesn't seem to work the way I thought it would. When I select the table that I want and also select the depth of the relationship, I only seem to get the table I select and no relationships at all. I've given up on it for now, but thanks for the link. I will check it out.

Have a good day!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-26 : 04:48:26

Let me think for a while... :)

Edited by - Stoad on 06/27/2003 05:09:28

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-27 : 05:04:29

Here is my approach:


____t0____      ______t1___________      ___t2___      ___t3___
p0   	p1        p1     p2     p3          p2            p3
----------      -------------------       ------        ------
0	11        1      222    3           2             3
00	1         11     22     3           22            33
000	11        111    222    333         222           333
----------      -------------------       -------      ---------


___t4___         ____t5_________         __t6__        ____t7_____
p4    p0          p5   p0   p6             p6            p7   p4
--------         ---------------          ----        -------------
4     00          5    00   666            6             7    444
44    00          55   0    6              66            77   44
444   0           555  0    66             666           777  444
--------         ---------------          -----        ------------



        REFERENCES                    OUTPUT
  __________t__________          _______tt_______
    n1    c    n2    s            nn    cc    vv
   --------------------         -------------------
    t0    p0   t0    0            t0    p0    0  << Initiator
    t0    p1   t1    0            t1    p1    11
    t1    p1   t1    0            t2    p2    22
    t1    p2   t2    0            t3    p3    3
    t1    p3   t3    0            t4    p4    444
    t2    p2   t2    0            t5    p5    55
    t3    p3   t3    0            t5    p5    555
    t4    p4   t4    0            t6    p6    6
    t4    p0   t0    0            t6    p6    66
    t5    p5   t5    0            t7    p7    7
    t5    p0   t0    0            t7    p7    777
    t5    p6   t6    0        ------------------------
    t6    p6   t6    0
    t7    p7   t7    0
    t7    p4   t4    0
   --------------------

=
All here is input data except of 10 'last' rows in tt table. What is the best way to get
them into tt? Of course, not for this special case of 8 tables only. Surely the populating
of tt table means solving glock's problem. Simplifying conditions:
1) first column in each table is its PK;
2) all columns are 'varchar(50)' data type;
3) names of PK-FK columns are the same thru all tables;
My newbie's solution (T-SQL 7.0) seems works but looks messy. Would like to see
smarter ones. :)

This my code populates tt table properly enough :) Of course, we can (inside of the
code) put together a Select Statement String along with inserting rows into tt table.
Then we get something like this:
'select * from t0, t1, ... where t0.p0='0' and t1.p1='11' and ... '

declare
@nn varchar(50), @cc varchar(50), @vv varchar(50), @j varchar(999),
@n1 varchar(50), @c varchar(50), @n varchar(50), @ss varchar(50)
----------------------- Initialization ----------------------
set @nn='t0' set @cc='p0' set @vv='0' --- choose any correct values
set @n=@nn set @ss=@nn delete from tt
insert into tt (nn, cc, vv) select @nn, @cc, @vv
update t set s='0'
-------------------------------------------------------------
while @@rowcount>0
begin
------------ processing of t0-t4 like relations -------------
declare abc cursor for select nn, cc, vv from tt
open abc
fetch next from abc into @nn, @cc, @vv
while @@fetch_status=0
begin
declare xyz cursor for
select a.n1, a.c from t a, t b
where a.n1=b.n1 and a.n1=a.n2 and b.n2=@nn and b.n1<>b.n2 and b.s='0'
open xyz
fetch next from xyz into @n1, @c
while @@fetch_status=0
begin
select @j='insert into tt (nn, cc, vv) select '+"'"+@n1+"'"+','
+"'"+@c+"'"+','+@c+' from '+@n1+' where '+@cc+'='+"'"+@vv+"'"
exec (@j)
select @j='update t set s=''2'' where n1='+"'"+@n1+"'"+
' and n2='+"'"+@n+"'"
exec (@j)
fetch next from xyz into @n1, @c
end
close xyz
deallocate xyz
fetch next from abc into @nn, @cc, @vv
end
close abc
deallocate abc
----------- processing of to-t1 like relations --------------
declare abc cursor for select nn, cc, vv from tt
open abc
fetch next from abc into @nn, @cc, @vv
while @@fetch_status=0
begin
if @ss<>@nn begin set @ss=@nn update t set s='2' where s='1' end
declare xyz cursor for
select a.n1, a.c from t a, t b where b.n1=@nn and b.n1<>b.n2
and a.n1=b.n2 and a.n1=a.n2 and b.s<>'2'
open xyz
fetch next from xyz into @n1, @c
while @@fetch_status=0
begin
select @j='insert into tt (nn, cc, vv) select '+"'"+@n1+"'"+','
+"'"+@c+"'"+','+@c+' from '+@nn+' where '+@cc+'='+"'"+@vv+"'"
exec (@j)
select @j='update t set s=''1'' where n1='+"'"+@nn+"'"+
' and n2='+"'"+@n1+"'"
exec (@j)
fetch next from xyz into @n1, @c
end
close xyz
deallocate xyz
fetch next from abc into @nn, @cc, @vv
end
close abc
deallocate abc
end

Edited by - Stoad on 07/02/2003 06:43:11

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-28 : 05:23:16

Just have tried to rewrite the code into recursive one but
it seems not going to be more compact nor plainer.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-06-28 : 13:29:26

Heh... what a recursion? Open BOL and read (capacity of objects):

Nested stored procedure levels 16(ver. 6.5) 32(ver. 7.0)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-02 : 06:40:06

This 'select' is for populating our (quasi) REFERENCES t table:

select d.n1, d.cn, d.n2, '0'
from (select a.table_name n1, b.column_name cn, c.table_name n2
from information_schema.tables a, information_schema.columns b,
information_schema.tables c where a.table_type='base table' and
a.table_name=b.table_name and c.table_type='base table' and
a.table_name<>'dtproperties' and c.table_name<>'dtproperties') d
where exists
(select 0 from sysindexes i, syscolumns c, sysobjects o, syscolumns c1
where o.id=object_id(d.n2)and o.id=c.id and o.id=i.id and (i.status &
0x800)=0x800 and c.name=index_col(d.n2,i.indid,c1.colid) and c1.colid
<=i.keycnt and c1.id=object_id(d.n2) and d.cn=convert(sysname,c.name))
order by 1, 2, 3

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources