looping within a query

i've got a database somewhat like a phonebook with these tables
contact - ID, name, primary_group_id, etc.
phone - ID, contact_id, number, etc.
group - ID, name, etc.
memberships - group_id, contact_id (identifies the group's contact manager)
group_links - parent_group_id, child_group_id

Some background information:
- the website it set up like a phone book, there is a hierarchy of groups (i.e. group A has subgroups b and c, that have subgroups b1, b2 and c1, c2, etc.).
- group managers are contacts in the database that are allowed to create contacts, groups/input phone numbers in any group that is a subgroup of a group they belong to.
- some groups don't have group managers


So basically i need to make a view that is going to return the following information about every phone in the database

phone#, phone_ext., ..., contact_name, contact_group, ..., manager_name, manager_group, etc.

Here is the tricky part. If the contact's group has no manager I want to loop through the page_links table (while consulting the memberships table) to find the nearest ancestor of the contact's group that has a group manager (this could be the parent group or it could be k steps up from the group)

i.e.
select phone#, phone_stuff,
contact_name, contact_group_id, contact_stuff,
manager_name, manager_group_id, manager_stuff
from phone LEFT JOIN contacts ON phone.contact_id = contacts.contact_id
LEFT JOIN memberships ON contacts.group_id = memberships.group_id
LEFT JOIN contacts manager ON memberships.contact_id = manager.contact_id
****LOOP If manager.contact_id is null find next parent group from page_links, try to find the parent group's manager**

 

SQL doesn't have any kind of looping--so you'll have to do this on the application side. Since you already have the logic there, should be pretty easy.

Side note: something about your database layout doesnt look right. I think you'd be better off coming your group and group_links table by adding a column to your group table called "parentID" in order to create a self-referential relationship to itself.

 

alright, well that's what i've been doing, i was just hoping there was some secret sql looping that i didn't know about

that would be the way it would make sense, but unfortunately in this database any group can have multiple parentID's so the group_links table was created to reduce redundancy/primary key changes in the groups table, although that may have caused extra time spent in joining the tables for queries

 

oracle database right?

 

sql server

 

I don't think that there is any looping in SQL Server. Therefore I suggest you to do it in C#

 

Well, yes, there is looping in SQL

declare @count int
declare @rowid int
declare @myprimarykey char(25)

set @count=1
set @rowid=(select max(rowid)from mytable)

while @count<=@rowid
begin

select
@myprimarykey=columnanme
from mytable where @count=@rowid

insert, update, delete from yournewtable where
yournewtable.pk=@myprimarykey

set @count=@count+1
end