Help me construct SQL Statement! ? Pls Help!

[Forri]

Hi

This is the problem:

I have a Users Table with a field named Reports_to which stores the ID of another user!

Now i have another table which stored Leave Applications of the users (or employees). These leave applications are only viewable by an assigned person for approval. This is found out by checking who reports to whom!

If I Nick report to JAck then when Jack loads the Approval Section he will see my Leave Applications for him to approve! But if Jack is to Report to Joe then Joe can see Jack's Applications and Nick's Applications! This can go on for ever!! (or quite)


How can i construct a simple SQL to retrieve all those that fall under a particular user?

Thanks
Nick

 

[r937]

simple sql? only in oracle

otherwise, you need recursion, which needs to be done in your scripting language (i.e. run a query in a loop, ee

http://wwww)

rudy

http://r937.com/

SQL Consulting

 

[swampBoogie]

There is a standard SQL solution for this using the WITH clause. It is supported by DB2 for instance.

 

[donutman]

This is where it is "permissable" (imho) to use cursors, even though it can ge done without them. This is also a good application for a recursive function that returns a table. If you want more help, then detail some table structure, etc.
-Karl

 

[r937]

swampBoogie, can you give an example of the syntax for those of use who have not forked over the money for the standard?

Karl, the table structure was given in the first post

Users
ID = primary key
Reports_to = foreign key referencing Users (ID)


rudy

http://r937.com/

SQL Consulting

 

[swampBoogie]

CREATE TABLE PARTLIST
(PART VARCHAR(8),
SUBPART VARCHAR(8),
QUANTITY INTEGER);

WITH RPL (PART,SUBPART,QUANTITY)AS
(SELECT ROOT.PART,ROOT.SUBPART,ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART ='01'
UNION ALL
SELECT CHILD.PART,CHILD.SUBPART,CHILD.QUANTITY
FROM RPL PARENT,PARTLIST CHILD
WHERE PARENT.SUBPART =CHILD.PART
)
SELECT DISTINCT PART,SUBPART,QUANTITY
FROM RPL
ORDER BY PART,SUBPART,QUANTITY

 

[r937]

thanks, swampBoogie

i guess i need to go back to school

is that supposed to be recursive?

rudy

http://r937.com/

SQL Consulting

 

[fredericofonseca]

But that only catters for one level if I'm not wrong. Correct me otherwise.

How would you adapt this to do it recursively? Or even up to 10 levels up?





Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com