question about dependent queries

[wireless]

Suppose I have the following table:

col1 col2
hammet jones
jlo afflect
afflect armand
wills snopt
armand hammet
jones smith

If someone choses armand, then I'd like to return
amand hammet jones smith

The first selection goes over to the second column, gets that value
and locates it back in column one and returns column 2 and so on.

One way of doing it is to set up a separate query for each one and
then construct a new query to get them all.

I'm thinking there's a more elegant way to do this. Any suggestions
would be appreciated.

-David

[louis nguyen]

Hi David,

One way is to use the old-fashioned join. Not sure how efficient the
query is though. - Louis

create table #T (x varchar(10),y varchar(10))
insert into #T values ('hammet','jones')
insert into #T values ('jlo','afflect')
insert into #T values ('afflect','armand')
insert into #T values ('wills','snopt')
insert into #T values ('armand','hammet')
insert into #T values ('jones','smith')

select a.x,a.y,b.y,c.y
from #T as a, #T as b, #T as c
where a.x='armand' and a.y=b.x and b.y=c.x

returns:
x y y y
---------- ---------- ---------- ----------
armand hammet jones smith

[David Brown]

On 4 Nov 2003 08:29:01 -0800, lo************@hotmail.com (louis
nguyen) wrote:

Hi David,

One way is to use the old-fashioned join. Not sure how efficient the
query is though. - Louis

create table #T (x varchar(10),y varchar(10))
insert into #T values ('hammet','jones')
insert into #T values ('jlo','afflect')
insert into #T values ('afflect','armand')
insert into #T values ('wills','snopt')
insert into #T values ('armand','hammet')
insert into #T values ('jones','smith')

select a.x,a.y,b.y,c.y
from #T as a, #T as b, #T as c
where a.x='armand' and a.y=b.x and b.y=c.x

Lou, thanks. This is what I was looking for and it improved my
understanding of joins.

This is a great forum.

regards,
-David